<?php

/**
 * Created by PhpStorm.
 * User: hgq
 * Date: 2016/6/13 0013
 * Time: 下午 2:39
 */
namespace app\models;

use app\librarys\Tools;
use Yii;
use app\librarys\DbCacheUtility;
use app\librarys\Inteface;

/**
 * Class Goods
 * @package app\models
 */
class Goods
{
    /**
     * 查询有下级机构的上级机构目录
     * @return array
     */
    public static function getInstitutionCatalog()
    {
        //查询SQL
        $sql = "select case when min(parent_code)='0' then '0' else '0' end as code,'系统平台' as name,'' as parent_code from t_institution where parent_code = '0'
                UNION select code,name,parent_code from t_institution where code in(select DISTINCT parent_code from t_institution)";
        //创建查询
        $cmd = \Yii::$app->db->createCommand($sql);
        //执行获取结果
        $inst_0 = $cmd->queryAll();
        return $inst_0;

    }

//    /**
//     * 获取机构列表（不包括0机构）
//     * @return array
//     */
//    public static function getAllInstitution()
//    {
//        //查询SQL
//        $sql = "select code,name,parent_code from t_institution ";
//        //创建查询
//        $cmd = \Yii::$app->db->createCommand($sql);
//        //执行获取结果
//        $inst_0 = $cmd->queryAll();
//        return $inst_0;
//
//    }

    /**
     * 根据机构编码查询下级机构列表
     * @return array
     */
    public static function getChildInstitution($institution_code)
    {
        $filterSQL = '';
        if ($institution_code == null || $institution_code == '') {
            return null;
        }
        //添加查询条件
        $filterSQL = $filterSQL . " and parent_code =:parent_code ";
        //查询SQL
        $sql = "select code,name,parent_code from t_institution where 1=1 $filterSQL";
        //创建查询
        $cmd = \Yii::$app->db->createCommand($sql);
        $cmd->bindValue(':parent_code', $institution_code);

        //执行获取结果
        $inst_0 = $cmd->queryAll();
        return $inst_0;
    }

    /**
     * 同步业务系统产品分类档案
     */
    public static function synGoodsCategory()
    {
        $goodscatelist = Inteface::getGoodsCategoryList();
        foreach ($goodscatelist as $value) {
            $code = $value->code;
            $name = $value->name;
            $parent_code = $value->parent_code;

            $sql = "call p_save_goods_category(:in_code,:in_name,:in_parent_code)";
            $cmd = \Yii::$app->db->createCommand($sql);
            $cmd->bindValue(':in_code', $code);
            $cmd->bindValue(':in_name', $name);
            $cmd->bindValue(':in_parent_code', $parent_code);
            $cmd->execute();
        }
    }

    /**
     * 同步业务系统产品档案
     * @throws \yii\db\Exception
     */
    public static function synGoods($isSynAllinfo)
    {
        $pageIndex = 1;
        $pageCount = 1000;
        $isExists = true;


        //修改产品表最后修改时间不等于当天的数据为 是否启用=0：禁用  是否上架=0：未上架 （因为在同步数据时没有类产品则说明产品已取消划入三方渠道）
        $updatesql = "UPDATE t_goods SET is_enabled=0 ,is_shelved=0 WHERE institution_code='0' and modify_datetime < NOW()";
        $cmd = \Yii::$app->db->createCommand($updatesql);
        $cmd->execute();

        while ($isExists) {

            $goodslist = Inteface::getGoodsList($pageIndex, $pageCount, $isSynAllinfo);
            if ($goodslist == null || $goodslist == '' || $goodslist <= 0) {
                $isExists = false;
            } else {
                foreach ($goodslist as $valuegoods) {
                    $code = $valuegoods->code;//编码
                    $name = $valuegoods->name;//名称
                    $format = $valuegoods->format;//规格
                    $model = $valuegoods->model;//型号
                    $brand = $valuegoods->brand;//品牌
                    $price = $valuegoods->price;//价格
                    $type = $valuegoods->type;//商品类型
                    $bind_type = $valuegoods->bind_type;//组合类型
                    $detail_html = $valuegoods->detail_html;//商品详情描述图文HTML
                    $is_activity = $valuegoods->is_activity;//是否活动商品 1：是 0：否
                    $is_trusteeship = $valuegoods->is_trusteeship;//是否托管商品 1：是 0：否
                    $is_shelved = $valuegoods->is_shelved;//是否上架 1：是 0：否
                    $is_newest = $valuegoods->is_newest;//是否新品 1：是 0：否
                    $category_code = $valuegoods->category_code;//分类编码
                    $group_code = $valuegoods->group_code;//同款产品编码
                    $group_item_name = $valuegoods->group_item_name;//同款产品差异名称
                    $display_name = $valuegoods->display_name;//产品显示标题
                    $summary = $valuegoods->summary;//概要描述
                    $is_crossborder = $valuegoods->is_crossborder;//是否跨境商品

                    if ($code != null && $code != '') {
                        $sql = "call p_save_goods(:in_code,:in_name,:in_format,:in_model,:in_brand,:in_price,:in_type,:in_bind_type,:in_detail_html,:in_is_activity,:in_is_trusteeship,:in_is_shelved,:in_is_newest,:in_category_code,:in_group_code,:in_group_item_name,:in_display_name,:in_summary,:in_is_crossborder,:is_syn_allinfo)";
                        $cmd = \Yii::$app->db->createCommand($sql);
                        $cmd->bindValue(':in_code', $code);
                        $cmd->bindValue(':in_name', $name);
                        $cmd->bindValue(':in_format', $format);
                        $cmd->bindValue(':in_model', $model);
                        $cmd->bindValue(':in_brand', $brand);
                        $cmd->bindValue(':in_price', $price);
                        $cmd->bindValue(':in_type', $type);
                        $cmd->bindValue(':in_bind_type', $bind_type);
                        $cmd->bindValue(':in_detail_html', $detail_html);
                        $cmd->bindValue(':in_is_activity', $is_activity);
                        $cmd->bindValue(':in_is_trusteeship', $is_trusteeship);
                        $cmd->bindValue(':in_is_shelved', $is_shelved);
                        $cmd->bindValue(':in_is_newest', $is_newest);
                        $cmd->bindValue(':in_category_code', $category_code);
                        $cmd->bindValue(':in_group_code', $group_code);
                        $cmd->bindValue(':in_group_item_name', $group_item_name);
                        $cmd->bindValue(':in_display_name', $display_name);
                        $cmd->bindValue(':in_summary', $summary);
                        $cmd->bindValue(':in_is_crossborder', $is_crossborder);
                        $cmd->bindValue(':is_syn_allinfo', $isSynAllinfo);
                        $cmd->execute();
                    }

                }
                $pageIndex = $pageIndex + 1;
            }
        }
    }

    /**
     * 同步业务系统产品HTML描述档案
     * @throws \yii\db\Exception
     */
    public static function synGoodsHtml()
    {
        $pageIndex = 1;
        $pageCount = 1000;
        $isExists = true;
        while ($isExists) {

            $goodslist = Inteface::getGoodsList($pageIndex, $pageCount, 'Y');
            if ($goodslist == null || $goodslist == '' || $goodslist <= 0) {
                $isExists = false;
            } else {
                foreach ($goodslist as $valuegoods) {
                    $code = $valuegoods->code;//编码
                    $detail_html = $valuegoods->detail_html;//商品详情描述图文HTML

                    if ($code != null && $code != '') {
                        $updatesql = " UPDATE t_goods_detail_html SET detail_html=:detail_html WHERE code=:code and institution_code='0'";
                        $cmd = \Yii::$app->db->createCommand($updatesql);
                        $cmd->bindValue(':code', $code);
                        $cmd->bindValue(':detail_html', $detail_html);
                        $cmd->execute();
                    }
                }
                $pageIndex = $pageIndex + 1;
            }
        }
    }


    /**
     * 同步业务系统产品图片档案
     */
    public static function synGoodsImage()
    {
        $goodsimglist = Inteface::getGoodsImgList();
        foreach ($goodsimglist as $valueimg) {
            $id = $valueimg->id;
            $goods_code = $valueimg->goods_code;
            $type = $valueimg->type;
            $image_url = $valueimg->image_url;

            $sql = "call p_save_goods_image(:in_core_code,:in_goods_code,:in_type,:in_image_url)";
            $cmd = \Yii::$app->db->createCommand($sql);
            $cmd->bindValue(':in_core_code', $id);
            $cmd->bindValue(':in_goods_code', $goods_code);
            $cmd->bindValue(':in_type', $type);
            $cmd->bindValue(':in_image_url', $image_url);
            $cmd->execute();
        }
    }

    /**
     * 同步机构产品
     * @param string $institution_code
     * @return false|null|string
     * @throws \yii\db\Exception
     */
    public static function synInstGoods($institution_code)
    {
        $sql = "call p_syn_institution_goods(:in_institution_code,@ostatus)";

        $cmd = \Yii::$app->db->createCommand($sql);
        $cmd->bindValue(':in_institution_code', $institution_code);

        $cmd->execute();

        $cmd = \Yii::$app->db->createCommand('SELECT @ostatus');
        $ostatus = $cmd->queryScalar();

        //20161125修改为程序循环 （以下代码循环较慢 还是用上面的过程）
//        $ostatus = 0;
//
//        //查询上级机构所有产品
//        $sql = " select t.code,t.name,t.display_name,t.summary,t.group_code,t.group_item_name,
//                t.format,t.model,t.brand,t.supply_price,t.type,t.bind_type,0 as is_modified,t.is_enabled,0 as is_shelved,t.is_newest,
//                t.is_activity,1 as is_show,t.is_trusteeship,t.is_crossborder,gc.category_code,case when tx.goodscode is not null then 'Y' else 'N' end ison,tx.isshelved,gcg.category_code as categorycode
//                FROM t_goods t
//                left join t_goods_category_goods gc on gc.goods_code = t.code and gc.institution_code = t.institution_code
//                left join (select tg.institution_code,tg.code as goodscode,tg.is_shelved as isshelved from t_goods tg where tg.institution_code =:institution_code) tx on tx.goodscode = t.code
//                left join t_goods_category_goods gcg on gcg.institution_code =:institution_code and gcg.goods_code = t.code
//                where t.institution_code = (select parent_code from t_institution where code = :institution_code)
//                  and NOT EXISTS(select code from t_goods_hide_child_institution where goods_code = t.code and child_institution_code = :institution_code)";
//        $cmd = \Yii::$app->db->createCommand($sql);
//        $cmd->bindValue(':institution_code', $institution_code);
//        $goodslist = $cmd->queryAll();
//        if ($goodslist != null && $goodslist != '') {
//            //同步上机构产品信息
//            foreach ($goodslist as $valuegoods) {
//                $code = $valuegoods['code'];//编码
//                $name = $valuegoods['name'];//名称
//                $display_name = $valuegoods['display_name'];//产品显示标题
//                $summary = $valuegoods['summary'];//概要描述
//                $group_code = $valuegoods['group_code'];//同款产品编码
//                $group_item_name = $valuegoods['group_item_name'];//同款产品差异名称
//                $format = $valuegoods['format'];//规格
//                $model = $valuegoods['model'];//型号
//                $brand = $valuegoods['brand'];//品牌
//                $supply_price = $valuegoods['supply_price'];//价格
//                $type = $valuegoods['type'];//商品类型
//                $bind_type = $valuegoods['bind_type'];//组合类型
//                $is_modified = $valuegoods['is_modified'];//
//                $is_enabled = $valuegoods['is_enabled'];//
//                $is_shelved = $valuegoods['is_shelved'];//是否上架 1：是 0：否
//                $is_newest = $valuegoods['is_newest'];//是否新品 1：是 0：否
//                $is_activity = $valuegoods['is_activity'];//
//                $is_show = $valuegoods['is_show'];//
//                $is_trusteeship = $valuegoods['is_trusteeship'];//
//                $is_crossborder = $valuegoods['is_crossborder'];//是否跨境商品
//                $category_code = $valuegoods['category_code'];//分类编码
//
//                $ison = $valuegoods['ison'];//在当前机构下是否已存在
//                $isshelved = $valuegoods['isshelved'];//在当前机构下的上下架状态
//                $categorycode = $valuegoods['categorycode'];//在当前机构下的产品与分类关联关系
//
//                if ($ison != null && $ison != '' && $ison=='Y') {
//                    //修改
//
//                    //如果上级机构产品下架 下级机构产品强制下架
//                    $instgoods_is_shelved = $isshelved;
//                    if($is_shelved == 0){
//                        $instgoods_is_shelved = 0;
//                    }
//
//                    $sql = "UPDATE t_goods SET name=:name,display_name=:display_name,summary=:summary,
//                                group_code=:group_code,group_item_name=:group_item_name,
//                                format=:format,model=:model,brand=:brand,settlement_price=:supply_price,
//                                type=:type,bind_type=:bind_type,is_enabled=:is_enabled,is_shelved=:is_shelved,
//                                is_trusteeship=:is_trusteeship,is_crossborder=:is_crossborder
//                                WHERE code =:code AND institution_code =:institution_code";
//                    $cmd = \Yii::$app->db->createCommand($sql);
//                    $cmd->bindValue(':name', $name);
//                    $cmd->bindValue(':display_name', $display_name);
//                    $cmd->bindValue(':summary', $summary);
//                    $cmd->bindValue(':group_code', $group_code);
//                    $cmd->bindValue(':group_item_name', $group_item_name);
//                    $cmd->bindValue(':format', $format);
//                    $cmd->bindValue(':model', $model);
//                    $cmd->bindValue(':brand', $brand);
//                    $cmd->bindValue(':supply_price', $supply_price);
//                    $cmd->bindValue(':type', $type);
//                    $cmd->bindValue(':bind_type', $bind_type);
//                    $cmd->bindValue(':is_enabled', $is_enabled);
//                    $cmd->bindValue(':is_shelved', $instgoods_is_shelved);
//                    $cmd->bindValue(':is_trusteeship', $is_trusteeship);
//                    $cmd->bindValue(':is_crossborder', $is_crossborder);
//                    $cmd->bindValue(':code', $code);
//                    $cmd->bindValue(':institution_code', $institution_code);
//                    $cmd->execute();
//
//                }else{
//                    //   新增
//                    $sql = "INSERT INTO t_goods
//                                (code,institution_code,name,display_name,summary,group_code,group_item_name,
//                                format,model,brand,settlement_price,selling_price,supply_price,
//                                type,bind_type,is_modified,is_enabled,is_shelved,is_newest,
//                                is_activity,is_show,is_trusteeship,is_crossborder,import_datetime)
//                                VALUES
//                                (:code, :institution_code,:name,:display_name,:summary,:group_code,:group_item_name,
//                                :format, :model, :brand, :supply_price, :supply_price, :supply_price,
//                                :type, :bind_type, :is_modified, :is_enabled, :is_shelved, :is_newest,
//                                :is_activity, :is_show, :is_trusteeship, :is_crossborder, NOW()) ";
//                    $cmd = \Yii::$app->db->createCommand($sql);
//                    $cmd->bindValue(':code', $code);
//                    $cmd->bindValue(':institution_code', $institution_code);
//                    $cmd->bindValue(':name', $name);
//                    $cmd->bindValue(':display_name', $display_name);
//                    $cmd->bindValue(':summary', $summary);
//                    $cmd->bindValue(':group_code', $group_code);
//                    $cmd->bindValue(':group_item_name', $group_item_name);
//                    $cmd->bindValue(':format', $format);
//                    $cmd->bindValue(':model', $model);
//                    $cmd->bindValue(':brand', $brand);
//                    $cmd->bindValue(':supply_price', $supply_price);
//                    $cmd->bindValue(':type', $type);
//                    $cmd->bindValue(':bind_type', $bind_type);
//                    $cmd->bindValue(':is_modified', $is_modified);
//                    $cmd->bindValue(':is_enabled', $is_enabled);
//                    $cmd->bindValue(':is_shelved', $is_shelved);
//                    $cmd->bindValue(':is_newest', $is_newest);
//                    $cmd->bindValue(':is_activity', $is_activity);
//                    $cmd->bindValue(':is_show', $is_show);
//                    $cmd->bindValue(':is_trusteeship', $is_trusteeship);
//                    $cmd->bindValue(':is_crossborder', $is_crossborder);
//                    $cmd->execute();
//                }
//
//                //产品与分类关联表
//                //修改
//                if ($categorycode!=null && $categorycode != '') {
//                    $sql = "UPDATE t_goods_category_goods SET category_code=:category_code
//                                WHERE goods_code =:code AND institution_code =:institution_code";
//                    $cmd = \Yii::$app->db->createCommand($sql);
//                    $cmd->bindValue(':category_code', $category_code);
//                    $cmd->bindValue(':code', $code);
//                    $cmd->bindValue(':institution_code', $institution_code);
//                    $cmd->execute();
//                }
//                //   新增
//                else {
//                    $sql = "INSERT INTO t_goods_category_goods(category_code,goods_code,institution_code)
//                                VALUES(:category_code,:code,:institution_code) ";
//                    $cmd = \Yii::$app->db->createCommand($sql);
//                    $cmd->bindValue(':category_code', $category_code);
//                    $cmd->bindValue(':code', $code);
//                    $cmd->bindValue(':institution_code', $institution_code);
//                    $cmd->execute();
//                }
//
//            }
//
//            //删除当前机构下原产品描述
//            $sql = "DELETE FROM t_goods_detail_html WHERE institution_code = :institution_code ";
//            $cmd = \Yii::$app->db->createCommand($sql);
//            $cmd->bindValue(':institution_code', $institution_code);
//            $cmd->execute();
//            //复制上级机构下产品描述
//            $sql = "INSERT INTO t_goods_detail_html(code,institution_code,detail_html)
//                    select t.code,:institution_code,t.detail_html from t_goods_detail_html t
//                    where t.institution_code = (select parent_code from t_institution where code = :institution_code)";
//            $cmd = \Yii::$app->db->createCommand($sql);
//            $cmd->bindValue(':institution_code', $institution_code);
//            $cmd->execute();
//        }

        return $ostatus;
    }


    /**
     * 同步上级机构产品
     * @param $institution_code
     * @return false|null|string
     */
    public static function sysHigherInstgoods($institution_code)
    {
        $sql = "call p_syn_institution_goods_bysuperupdate(:in_institution_code,@ostatus)";

        $cmd = \Yii::$app->db->createCommand($sql);
        $cmd->bindValue(':in_institution_code', $institution_code);

        $cmd->execute();

        $cmd = \Yii::$app->db->createCommand('SELECT @ostatus');
        $ostatus = $cmd->queryScalar();

        return $ostatus;
    }

    /**
     * 同步机构商品分类
     * @param string $institution_code
     * @return false|null|string
     * @throws \yii\db\Exception
     */
    public static function synInstGoodsCategory($institution_code)
    {
        $sql = "call p_syn_institution_goods_category(:in_institution_code,@ostatus)";

        $cmd = \Yii::$app->db->createCommand($sql);
        $cmd->bindValue(':in_institution_code', $institution_code);

        $cmd->execute();

        $cmd = \Yii::$app->db->createCommand('SELECT @ostatus');
        $ostatus = $cmd->queryScalar();

        return $ostatus;
    }

    /**
     * 同步上级机构商品分类到下级
     * @param $institution_code
     * @return false|null|string
     */
    public static function sysHigherInstGoodsCategory($institution_code)
    {
        $sql = "call p_syn_institution_goods_category_bysuperupdate(:in_institution_code,@ostatus)";

        $cmd = \Yii::$app->db->createCommand($sql);
        $cmd->bindValue(':in_institution_code', $institution_code);

        $cmd->execute();

        $cmd = \Yii::$app->db->createCommand('SELECT @ostatus');
        $ostatus = $cmd->queryScalar();

        return $ostatus;
    }

    /**
     * 同步更新下级机构产品信息
     */
    public function sysUnderInstitutionGoods($institution_code, $institution_code_list)
    {

        if ($institution_code_list != null && $institution_code_list != '') {
            foreach ($institution_code_list as $inst_code) {
                //根据下级机构编号同步上级机构产品数据
                $sql = "call p_syn_institution_goods_bysuperupdate(:in_institution_code,@ostatus)";
                $cmd = \Yii::$app->db->createCommand($sql);
                $cmd->bindValue(':in_institution_code', $inst_code);
                $cmd->execute();
                $cmd = \Yii::$app->db->createCommand('SELECT @ostatus');
                $ostatus = $cmd->queryScalar();
            }
        }
    }

    /**
     * 同步更新下级机构产品分类信息
     */
    public function sysUnderInstitutionGoodsCategory($institution_code, $institution_code_list)
    {

        if ($institution_code_list != null && $institution_code_list != '') {
            foreach ($institution_code_list as $inst_code) {
                //根据下级机构编号同步上级机构产品分类数据
                $sql = "call p_syn_institution_goods_category_bysuperupdate(:in_institution_code,@ostatus)";
                $cmd = \Yii::$app->db->createCommand($sql);
                $cmd->bindValue(':in_institution_code', $inst_code);
                $cmd->execute();
                $cmd = \Yii::$app->db->createCommand('SELECT @ostatus');
                $ostatus = $cmd->queryScalar();
            }
        }
    }

    /**
     * 同步更新下级机构产品扩展分类信息
     */
    public function sysUnderInstitutionGoodsExtendCategory($institution_code, $institution_code_list)
    {

        if ($institution_code_list != null && $institution_code_list != '') {
            foreach ($institution_code_list as $institution_code) {
                //根据下级机构编号同步上级机构产品扩展分类数据  方法synGoodsGoodsCategory内有删除当前数据，所以在此不用操作删除
                self::synGoodsGoodsCategory($institution_code);
            }
        }
    }

    /**
     * 根据机构编码查询上级机构产品数量
     * @param $institution_code
     * @return false|null|string
     */
    public static function getParentInstGoodsCountByInstcode($institution_code)
    {
        $result = 0;
        if ($institution_code != null && $institution_code != '') {
            //查询SQL
            $sql = "select count(code) from t_goods where institution_code=(select parent_code from t_institution where code =:institution_code) ";
            //创建查询
            $cmd = \Yii::$app->db->createCommand($sql);
            $cmd->bindValue(':institution_code', $institution_code);
            //执行获取结果
            $result = $cmd->queryScalar();
        }
        return $result;
    }

    /**
     * 查询产品总数
     * @param string $institution_code
     * @param string|null $code
     * @param string|null $name
     * @param string|null $group_code
     * @param string|null $group_item_name
     * @param string|null $brand
     * @param string|null $type
     * @param int|null $is_enabled [0/1]
     * @param int|null $is_shelved [0/1]
     * @param int|null $is_activity [0/1]
     * @param int|null $is_trusteeship [0/1]
     * @param int|null $is_pcr = [0/1]
     * @return false|null|string
     */
    public static function getGoodsCountByfilters($institution_code, $code = null, $name = null, $group_code = null, $group_item_name = null, $brand = null, $type = null, $is_enabled = null, $is_shelved = null, $is_newest = null, $is_hot = null, $is_top = null, $is_activity = null, $is_trusteeship = null, $is_pcr = null, $jsjg_form = null, $jsjg_to = null, $jgdb1 = null, $jgdb2 = null, $jgdb3 = null)
    {
        $result = null;
//        if ($institution_code != null && $institution_code != '') {//20170116修改 机构可以空
            $filterSQL = '';
            //添加查询条件
            if ($institution_code != null && $institution_code != '') {//20170116修改 如果机构传入为空 则认为是非系统产品池的查询 此时则不能查询机构等于0的产品
                $filterSQL = $filterSQL . " and t.institution_code=:institution_code ";
            }else{
                $filterSQL = $filterSQL . " and t.institution_code!='0' ";
            }
            if ($code != null && $code != '') {
                $filterSQL = $filterSQL . " and t.code like :filter_code ";
            }
            if ($name != null && $name != '') {
                $filterSQL = $filterSQL . " and t.name like :filter_name ";
            }
            if ($group_code != null && $group_code != '') {
                $filterSQL = $filterSQL . " and t.group_code like :filter_group_code ";
            }
            if ($group_item_name != null && $group_item_name != '') {
                $filterSQL = $filterSQL . " and t.group_item_name like :filter_group_item_name ";
            }
            if ($brand != null && $brand != '') {
                $filterSQL = $filterSQL . " and t.brand like :filter_brand ";
            }
            if ($type != null && $type != '') {
                $filterSQL = $filterSQL . " and t.type = :filter_type ";
            }
            if ($is_enabled != null && $is_enabled != '' && $is_enabled != '-1') {
                $filterSQL = $filterSQL . " and t.is_enabled = :filter_is_enabled ";
            }
            if ($is_shelved != null && $is_shelved != '' && $is_shelved != '-1') {
                $filterSQL = $filterSQL . " and t.is_shelved = :filter_is_shelved ";
            }
            if ($is_newest != null && $is_newest != '' && $is_newest != '-1') {
                $filterSQL = $filterSQL . " and t.is_newest = :filter_is_newest ";
            }
            if ($is_hot != null && $is_hot != '' && $is_hot != '-1') {
                $filterSQL = $filterSQL . " and t.is_hot = :filter_is_hot ";
            }
            if ($is_top != null && $is_top != '' && $is_top != '-1') {
                $filterSQL = $filterSQL . " and t.is_top = :filter_is_top ";
            }
            if ($is_activity != null && $is_activity != '' && $is_activity != '-1') {
                $filterSQL = $filterSQL . " and t.is_activity = :filter_is_activity ";
            }
            if ($is_trusteeship != null && $is_trusteeship != '' && $is_trusteeship != '-1') {
                $filterSQL = $filterSQL . " and t.is_trusteeship = :filter_is_trusteeship ";
            }
            if ($is_pcr != null && $is_pcr != '' && $is_pcr != '-1' && $is_pcr != 0) {
                $filterSQL = $filterSQL . " and (case when pr.oper_datetime is not null then 1 else 0 end)=:is_pcr ";
            }
            if ($jsjg_form != null && $jsjg_form != '' && ($jsjg_to == null || $jsjg_to =='')) {
                $filterSQL = $filterSQL . " and (t.settlement_price >= :filter_settlement_price_form or t.selling_price >= :filter_settlement_price_form or t.supply_price >= :filter_settlement_price_form) ";
            }
            if ($jsjg_to != null && $jsjg_to != '' && ($jsjg_form == null || $jsjg_form =='')) {
                $filterSQL = $filterSQL . " and (t.settlement_price <= :filter_settlement_price_to or t.selling_price <= :filter_settlement_price_to or t.supply_price <= :filter_settlement_price_to) ";
            }
            if ($jsjg_form != null && $jsjg_form != '' &&  $jsjg_to != null && $jsjg_to != '') {
                $filterSQL = $filterSQL . " and ((t.settlement_price >= :filter_settlement_price_form and t.settlement_price <= :filter_settlement_price_to) or (t.selling_price >= :filter_settlement_price_form and t.selling_price <= :filter_settlement_price_to) or (t.supply_price >= :filter_settlement_price_form and t.supply_price <= :filter_settlement_price_to))";
            }
            if ($jgdb1 != null && $jgdb1 != '' && $jgdb1 != '-1') {
                if($jgdb1==1){
                    //结算价格 != 销售价格
                    $filterSQL = $filterSQL . " and t.settlement_price != t.selling_price ";
                } else if($jgdb1==2){
                    //结算价格 > 销售价格
                    $filterSQL = $filterSQL . " and t.settlement_price > t.selling_price ";
                } else if($jgdb1==3){
                    //结算价格 < 销售价格
                    $filterSQL = $filterSQL . " and t.settlement_price < t.selling_price ";
                }
            }
            if ($jgdb2 != null && $jgdb2 != '' && $jgdb2 != '-1') {
                if($jgdb2==1){
                    //结算价格 != 供应价格
                    $filterSQL = $filterSQL . " and t.settlement_price != t.supply_price ";
                } else if($jgdb2==2){
                    //结算价格 > 供应价格
                    $filterSQL = $filterSQL . " and t.settlement_price > t.supply_price ";
                } else if($jgdb2==3){
                    //结算价格 < 供应价格
                    $filterSQL = $filterSQL . " and t.settlement_price < t.supply_price ";
                }
            }
            if ($jgdb3 != null && $jgdb3 != '' && $jgdb3 != '-1') {
                if($jgdb3==1){
                    //销售价格 != 供应价格
                    $filterSQL = $filterSQL . " and t.selling_price != t.supply_price ";
                } else if($jgdb3==2){
                    //销售价格 > 供应价格
                    $filterSQL = $filterSQL . " and t.selling_price > t.supply_price ";
                } else if($jgdb3==3){
                    //销售价格 < 供应价格
                    $filterSQL = $filterSQL . " and t.selling_price < t.supply_price ";
                }
            }
            //查询SQL
            $sql = "select count(t.code) from t_goods t
                    left join (select child_institution_code,goods_code,max(oper_datetime) as oper_datetime from t_goods_supply_price_records where is_read = 0 group by child_institution_code,goods_code)
                     pr on pr.goods_code = t.code and pr.child_institution_code = t.institution_code
                    where t.institution_code is not null $filterSQL ";

            //创建查询
            $cmd = \Yii::$app->db->createCommand($sql);
            //绑定参数
            if ($institution_code != null && $institution_code != '') {
                $cmd->bindValue(':institution_code', $institution_code);
            }
            if ($code != null && $code != '') {
                $filter_code = '%' . $code . '%';
                $cmd->bindValue(':filter_code', $filter_code);
            }
            if ($name != null && $name != '') {
                $filter_name = '%' . $name . '%';
                $cmd->bindValue(':filter_name', $filter_name);
            }
            if ($group_code != null && $group_code != '') {
                $filter_group_code = '%' . $group_code . '%';
                $cmd->bindValue(':filter_group_code', $filter_group_code);
            }
            if ($group_item_name != null && $group_item_name != '') {
                $filter_group_item_name = '%' . $group_item_name . '%';
                $cmd->bindValue(':filter_group_item_name', $filter_group_item_name);
            }
            if ($brand != null && $brand != '') {
                $filter_brand = '%' . $brand . '%';
                $cmd->bindValue(':filter_brand', $filter_brand);
            }
            if ($type != null && $type != '') {
                $cmd->bindValue(':filter_type', $type);
            }
            if ($is_enabled != null && $is_enabled != '' && $is_enabled != '-1') {
                $cmd->bindValue(':filter_is_enabled', $is_enabled);
            }
            if ($is_shelved != null && $is_shelved != '' && $is_shelved != '-1') {
                $cmd->bindValue(':filter_is_shelved', $is_shelved);
            }
            if ($is_newest != null && $is_newest != '' && $is_newest != '-1') {
                $cmd->bindValue(':filter_is_newest', $is_newest);
            }
            if ($is_hot != null && $is_hot != '' && $is_hot != '-1') {
                $cmd->bindValue(':filter_is_hot', $is_hot);
            }
            if ($is_top != null && $is_top != '' && $is_top != '-1') {
                $cmd->bindValue(':filter_is_top', $is_top);
            }
            if ($is_activity != null && $is_activity != '' && $is_activity != '-1') {
                $cmd->bindValue(':filter_is_activity', $is_activity);
            }
            if ($is_trusteeship != null && $is_trusteeship != '' && $is_trusteeship != '-1') {
                $cmd->bindValue(':filter_is_trusteeship', $is_trusteeship);
            }
            if ($is_pcr != null && $is_pcr != '' && $is_pcr != '-1' && $is_pcr != 0) {
                $cmd->bindValue(':is_pcr', $is_pcr);
            }
            if ($jsjg_form != null && $jsjg_form != '') {
                $cmd->bindValue(':filter_settlement_price_form', $jsjg_form);
            }
            if ($jsjg_to != null && $jsjg_to != '') {
                $cmd->bindValue(':filter_settlement_price_to', $jsjg_to);
            }
            //执行获取结果
            $result = $cmd->queryScalar();
//        }
        return $result;
    }

    /**
     * 查询产品列表
     * @param string $institution_code
     * @param string|null $code
     * @param string|null $name
     * @param string|null $brand
     * @param string|null $type
     * @param int|null $is_enabled [0/1]
     * @param int|null $is_shelved [0/1]
     * @param int|null $is_activity [0/1]
     * @param int|null $is_trusteeship [0/1]
     * @param int|null $is_pcr = [0/1]
     * @param int|null $page [start 1]
     * @param int|null $pagesize
     * @return array
     */
    public static function getGoodsListByfilters($institution_code, $code = null, $name = null, $group_code = null, $group_item_name = null, $brand = null, $type = null, $is_enabled = null, $is_shelved = null, $is_newest = null, $is_hot = null, $is_top = null, $is_activity = null, $is_trusteeship = null, $is_pcr = null, $page = 1, $pagesize = 10, $jsjg_form = null, $jsjg_to = null, $jgdb1 = null, $jgdb2 = null, $jgdb3 = null)
    {
        $result = null;
//        if ($institution_code != null && $institution_code != '') {//20170116修改 机构可以空
            $filterSQL = '';

            //添加查询条件
            if ($institution_code != null && $institution_code != '') {//20170116修改 如果机构传入为空 则认为是非系统产品池的查询 此时则不能查询机构等于0的产品
                $filterSQL = $filterSQL . " and t.institution_code=:institution_code ";
            }else{
                $filterSQL = $filterSQL . " and t.institution_code!='0' ";
            }
            if ($code != null && $code != '') {
                $filterSQL = $filterSQL . " and t.code like :filter_code ";
            }
            if ($name != null && $name != '') {
                $filterSQL = $filterSQL . " and t.name like :filter_name ";
            }
            if ($group_code != null && $group_code != '') {
                $filterSQL = $filterSQL . " and t.group_code like :filter_group_code ";
            }
            if ($group_item_name != null && $group_item_name != '') {
                $filterSQL = $filterSQL . " and t.group_item_name like :filter_group_item_name ";
            }
            if ($brand != null && $brand != '') {
                $filterSQL = $filterSQL . " and t.brand like :filter_brand ";
            }
            if ($type != null && $type != '') {
                $filterSQL = $filterSQL . " and t.type = :filter_type ";
            }
            if ($is_enabled != null && $is_enabled != '' && $is_enabled != '-1') {
                $filterSQL = $filterSQL . " and t.is_enabled = :filter_is_enabled ";
            }
            if ($is_shelved != null && $is_shelved != '' && $is_shelved != '-1') {
                $filterSQL = $filterSQL . " and t.is_shelved = :filter_is_shelved ";
            }
            if ($is_newest != null && $is_newest != '' && $is_newest != '-1') {
                $filterSQL = $filterSQL . " and t.is_newest = :filter_is_newest ";
            }
            if ($is_hot != null && $is_hot != '' && $is_hot != '-1') {
                $filterSQL = $filterSQL . " and t.is_hot = :filter_is_hot ";
            }
            if ($is_top != null && $is_top != '' && $is_top != '-1') {
                $filterSQL = $filterSQL . " and t.is_top = :filter_is_top ";
            }
            if ($is_activity != null && $is_activity != '' && $is_activity != '-1') {
                $filterSQL = $filterSQL . " and t.is_activity = :filter_is_activity ";
            }
            if ($is_trusteeship != null && $is_trusteeship != '' && $is_trusteeship != '-1') {
                $filterSQL = $filterSQL . " and t.is_trusteeship = :filter_is_trusteeship ";
            }
            if ($is_pcr != null && $is_pcr != '' && $is_pcr != '-1' && $is_pcr != 0) {
                $filterSQL = $filterSQL . " and (case when pr.oper_datetime is not null then 1 else 0 end)=:is_pcr ";
            }
            if ($jsjg_form != null && $jsjg_form != '' && ($jsjg_to == null || $jsjg_to =='')) {
                $filterSQL = $filterSQL . " and (t.settlement_price >= :filter_settlement_price_form or t.selling_price >= :filter_settlement_price_form or t.supply_price >= :filter_settlement_price_form) ";
            }
            if ($jsjg_to != null && $jsjg_to != '' && ($jsjg_form == null || $jsjg_form =='')) {
                $filterSQL = $filterSQL . " and (t.settlement_price <= :filter_settlement_price_to or t.selling_price <= :filter_settlement_price_to or t.supply_price <= :filter_settlement_price_to) ";
            }
            if ($jsjg_form != null && $jsjg_form != '' &&  $jsjg_to != null && $jsjg_to != '') {
                $filterSQL = $filterSQL . " and ((t.settlement_price >= :filter_settlement_price_form and t.settlement_price <= :filter_settlement_price_to) or (t.selling_price >= :filter_settlement_price_form and t.selling_price <= :filter_settlement_price_to) or (t.supply_price >= :filter_settlement_price_form and t.supply_price <= :filter_settlement_price_to))";
            }
            if ($jgdb1 != null && $jgdb1 != '' && $jgdb1 != '-1') {
                if($jgdb1==1){
                    //结算价格 != 销售价格
                    $filterSQL = $filterSQL . " and t.settlement_price != t.selling_price ";
                } else if($jgdb1==2){
                    //结算价格 > 销售价格
                    $filterSQL = $filterSQL . " and t.settlement_price > t.selling_price ";
                } else if($jgdb1==3){
                    //结算价格 < 销售价格
                    $filterSQL = $filterSQL . " and t.settlement_price < t.selling_price ";
                }
            }
            if ($jgdb2 != null && $jgdb2 != '' && $jgdb2 != '-1') {
                if($jgdb2==1){
                    //结算价格 != 供应价格
                    $filterSQL = $filterSQL . " and t.settlement_price != t.supply_price ";
                } else if($jgdb2==2){
                    //结算价格 > 供应价格
                    $filterSQL = $filterSQL . " and t.settlement_price > t.supply_price ";
                } else if($jgdb2==3){
                    //结算价格 < 供应价格
                    $filterSQL = $filterSQL . " and t.settlement_price < t.supply_price ";
                }
            }
            if ($jgdb3 != null && $jgdb3 != '' && $jgdb3 != '-1') {
                if($jgdb3==1){
                    //销售价格 != 供应价格
                    $filterSQL = $filterSQL . " and t.selling_price != t.supply_price ";
                } else if($jgdb3==2){
                    //销售价格 > 供应价格
                    $filterSQL = $filterSQL . " and t.selling_price > t.supply_price ";
                } else if($jgdb3==3){
                    //销售价格 < 供应价格
                    $filterSQL = $filterSQL . " and t.selling_price < t.supply_price ";
                }
            }

            //计算起始数据行
            if ($page <= 0) $page = 0;
            $start = ($page - 1) * $pagesize;
            //查询SQL
            $sql = "select t.*,t4.image_url as goods_image,case when pr.oper_datetime is not null then 1 else 0 end as ischangesprice,prr.supply_price_old,prr.supply_price_new
                    ,case when t.institution_code ='0' then '系统产品池' else jg.`name` end institution_name
                    from t_goods t
                    left join (select child_institution_code,goods_code,max(oper_datetime) as oper_datetime from t_goods_supply_price_records where is_read = 0 group by child_institution_code,goods_code)
                     pr on pr.goods_code = t.code and pr.child_institution_code = t.institution_code
                    left join t_goods_supply_price_records prr on prr.child_institution_code = pr.child_institution_code and prr.goods_code = pr.goods_code and prr.oper_datetime = pr.oper_datetime and prr.is_read = 0
                    left join t_goods_image t4 on t4.goods_code=t.`code` and t4.type=1
                    left join t_institution jg on jg.`code` = t.institution_code
                    where t.institution_code is not null $filterSQL LIMIT $start,$pagesize ";

            //创建查询
            $cmd = \Yii::$app->db->createCommand($sql);
            if ($institution_code != null && $institution_code != '') {
                $cmd->bindValue(':institution_code', $institution_code);
            }
            //绑定参数
            if ($code != null && $code != '') {
                $filter_code = '%' . $code . '%';
                $cmd->bindValue(':filter_code', $filter_code);
            }
            if ($name != null && $name != '') {
                $filter_name = '%' . $name . '%';
                $cmd->bindValue(':filter_name', $filter_name);
            }
            if ($group_code != null && $group_code != '') {
                $filter_group_code = '%' . $group_code . '%';
                $cmd->bindValue(':filter_group_code', $filter_group_code);
            }
            if ($group_item_name != null && $group_item_name != '') {
                $filter_group_item_name = '%' . $group_item_name . '%';
                $cmd->bindValue(':filter_group_item_name', $filter_group_item_name);
            }
            if ($brand != null && $brand != '') {
                $filter_brand = '%' . $brand . '%';
                $cmd->bindValue(':filter_brand', $filter_brand);
            }
            if ($type != null && $type != '') {
                $cmd->bindValue(':filter_type', $type);
            }
            if ($is_enabled != null && $is_enabled != '' && $is_enabled != '-1') {
                $cmd->bindValue(':filter_is_enabled', $is_enabled);
            }
            if ($is_shelved != null && $is_shelved != '' && $is_shelved != '-1') {
                $cmd->bindValue(':filter_is_shelved', $is_shelved);
            }
            if ($is_newest != null && $is_newest != '' && $is_newest != '-1') {
                $cmd->bindValue(':filter_is_newest', $is_newest);
            }
            if ($is_hot != null && $is_hot != '' && $is_hot != '-1') {
                $cmd->bindValue(':filter_is_hot', $is_hot);
            }
            if ($is_top != null && $is_top != '' && $is_top != '-1') {
                $cmd->bindValue(':filter_is_top', $is_top);
            }
            if ($is_activity != null && $is_activity != '' && $is_activity != '-1') {
                $cmd->bindValue(':filter_is_activity', $is_activity);
            }
            if ($is_trusteeship != null && $is_trusteeship != '' && $is_trusteeship != '-1') {
                $cmd->bindValue(':filter_is_trusteeship', $is_trusteeship);
            }
            if ($is_pcr != null && $is_pcr != '' && $is_pcr != '-1' && $is_pcr != 0) {
                $cmd->bindValue(':is_pcr', $is_pcr);
            }
            if ($jsjg_form != null && $jsjg_form != '') {
                $cmd->bindValue(':filter_settlement_price_form', $jsjg_form);
            }
            if ($jsjg_to != null && $jsjg_to != '') {
                $cmd->bindValue(':filter_settlement_price_to', $jsjg_to);
            }
            //执行获取结果
            $result = $cmd->queryAll();
//        }

        return $result;
    }

    /**
     * 查询机构结算价格变更记录
     * @param string $institution_code
     * @return array|null
     */
    public static function getInstGoodsSupplyPriceCRecords($institution_code)
    {
        $result = null;
        if ($institution_code != null && $institution_code != '') {

            //查询SQL
            $sql = "select t.* from t_goods_supply_price_records t inner join t_goods g on g.institution_code = t.child_institution_code and g.code = t.goods_code where t.child_institution_code =:institution_code and t.is_read = 0 ";
            //创建查询
            $cmd = \Yii::$app->db->createCommand($sql);
            $cmd->bindValue(':institution_code', $institution_code);

            //执行获取结果
            $result = $cmd->queryAll();
        }

        return $result;
    }

    /**
     * 根据机构编号和产品编码查询产品详情
     * @param string $institution_code
     * @param string $goods_code
     * @return mixed|null
     */
    public static function getGoodsInfoByCode($institution_code, $goods_code)
    {
        $sql = "SELECT t.*,c.code as category_code,c.name as category_name, t3.detail_html,jg.is_custom_price FROM t_goods t
                left join t_goods_category_goods cg on cg.goods_code = t.code and cg.institution_code = t.institution_code
                left join t_goods_category c on c.code = cg.category_code and c.institution_code = cg.institution_code
                left join t_goods_detail_html t3 on t3.`code`=t.`code` and t3.institution_code=t.institution_code
                left join t_institution jg on jg.`code` = t.institution_code
                where t.institution_code=:institution_code and t.code=:goods_code ";
        $cmd = \Yii::$app->db->createCommand($sql);
        $cmd->bindValue(':institution_code', $institution_code);
        $cmd->bindValue(':goods_code', $goods_code);
        $result = $cmd->queryOne();
//        $result = DbCacheUtility::queryAll($cmd);
        if ($result == false) {
            return null;
        } else {
            return $result;
        }
    }

    /**
     * 获取商品详情(仅包含商品用于前端商城所要展示的数据)
     * @param string $institutionCode
     * @param string $goodsCode
     * @return array|false|null
     */
    public static function getGoodsDetail($institutionCode, $goodsCode)
    {
        $sql = "
        SELECT t.`code`, t.`name`, t.model, t.format, t.brand, t.selling_price, t.selling_price*t5.integral_rate as selling_integral, t5.integral_unit, t.type, t.bind_type, t6.detail_html, t.is_enabled, t.is_shelved, t.is_show, t4.image_url as goods_image,c.code as category_code,c.name as category_name
        FROM t_goods t
        left join t_goods_category_goods cg on cg.goods_code = t.code and cg.institution_code = t.institution_code
        left join t_goods_category c on c.code = cg.category_code and c.institution_code = cg.institution_code
        left join t_goods_image t4 on t4.goods_code=t.`code` and t4.type=1
        left join t_institution t5 on t5.`code`=t.institution_code
        left join t_goods_detail_html t6 on t6.`code`=t.`code` and t6.institution_code=t.institution_code
        where t.institution_code=:institution_code and t.code=:goods_code ";
        $cmd = \Yii::$app->db->createCommand($sql);
        $cmd->bindValue(':institution_code', $institutionCode);
        $cmd->bindValue(':goods_code', $goodsCode);
        $result = $cmd->queryOne();
        if ($result != false) {
            $cmd = Yii::$app->db->createCommand('SELECT * FROM t_goods_image WHERE goods_code=:goodsCode AND type=2');
            $cmd->bindValue(':goodsCode', $goodsCode);
            $result['goods_image_list'] = $cmd->queryAll();
            return $result;
        } else {
            return null;
        }
    }

    /**
     * 根据产品编号和图片类别查询产品图片
     * @param string $goods_code
     * @param string $type
     * @return array
     */
    public static function getGoodsImage($goods_code, $type)
    {
        $sql = "select image_url from t_goods_image where type =:type and goods_code =:goods_code ";
        $cmd = \Yii::$app->db->createCommand($sql);
        $cmd->bindValue(':type', $type);
        $cmd->bindValue(':goods_code', $goods_code);
//        $result = $cmd->queryAll();
        $result = DbCacheUtility::queryAll($cmd);
        if (count($result) > 0) {
            return $result;
        } else {
            return [];
        }
    }

    /**
     * 查询商品针对旗下机构不可见数据总数
     * @param string $institution_code
     * @param string $child_institution_code
     * @return false|null|string
     */
    public static function getGoodsHideChildCountByfilters($institution_code, $child_institution_code)
    {
        $filterSQL = '';
        //添加查询条件
        if ($child_institution_code != null && $child_institution_code != '') {
            $filterSQL = $filterSQL . " and child_institution_code = :child_institution_code ";
        }
        //查询SQL
        $sql = "select count(goods_code) from t_goods_hide_child_institution where institution_code=:institution_code $filterSQL ";

        //创建查询
        $cmd = \Yii::$app->db->createCommand($sql);
        $cmd->bindValue(':institution_code', $institution_code);
        //绑定参数
        if ($child_institution_code != null && $child_institution_code != '') {
            $cmd->bindValue(':child_institution_code', $child_institution_code);
        }
        //执行获取结果
        $result = $cmd->queryScalar();
        return $result;
    }

    /**
     * 查询商品针对旗下机构不可见数据列表
     * @param string $institution_code
     * @param string $child_institution_code
     * @param int $page [start 1]
     * @param int $pagesize
     * @return array
     */
    public static function getGoodsHideChildListByfilters($institution_code, $child_institution_code, $page = 1, $pagesize = 10)
    {
        $filterSQL = '';
        //添加查询条件
        if ($child_institution_code != null && $child_institution_code != '') {
            $filterSQL = $filterSQL . " and t.child_institution_code = :child_institution_code ";
        }
        //计算起始数据行
        if ($page <= 0) $page = 0;
        $start = ($page - 1) * $pagesize;
        //查询SQL
        $sql = "select t.*,g.name as goods_name,i.name as child_institution_name from t_goods_hide_child_institution t
                left join t_goods g on g.code = t.goods_code and g.institution_code = t.institution_code
                left join t_institution i on i.code = t.child_institution_code
                where t.institution_code=:institution_code $filterSQL LIMIT $start,$pagesize ";

        //创建查询
        $cmd = \Yii::$app->db->createCommand($sql);
        //绑定参数
        $cmd->bindValue(':institution_code', $institution_code);
        if ($child_institution_code != null && $child_institution_code != '') {
            $cmd->bindValue(':child_institution_code', $child_institution_code);
        }
        //执行获取结果
        $result = $cmd->queryAll();
        return $result;
    }

    /**
     * 通过传入的 上下级机构 产品编号 名称 品牌条件查询出上级机构的产品列表 以及当前机构是否可见状态
     * @param string $super_inst_code
     * @param string $child_inst_code
     * @param string $code
     * @param string $name
     * @param string $brand
     * @return array
     */
    public static function getChildHideInstGoods($super_inst_code, $child_inst_code, $code, $name, $brand, $is_hide)
    {
        $filterSQL = '';

        //添加查询条件
        if ($code != null && $code != '') {
            $filterSQL = $filterSQL . " and t.code like :filter_code ";
        }
        if ($name != null && $name != '') {
            $filterSQL = $filterSQL . " and t.name like :filter_name ";
        }
        if ($brand != null && $brand != '') {
            $filterSQL = $filterSQL . " and t.brand like :filter_brand ";
        }
        if ($is_hide != null && $is_hide != '') {
            $filterSQL = $filterSQL . " and case when h.goods_code is not null then 0 else 1 end =:filter_is_hide ";
        }

        //查询SQL
        $sql = "select t.code,t.name,t.brand,case when h.goods_code is not null then 0 else 1 end as is_hide from t_goods t
                left join t_goods_hide_child_institution h on h.institution_code = t.institution_code
                and h.goods_code = t.code and h.child_institution_code =:child_institution_code
                where t.institution_code =:institution_code $filterSQL ";
        //创建查询
        $cmd = \Yii::$app->db->createCommand($sql);
        $cmd->bindValue(':child_institution_code', $child_inst_code);
        $cmd->bindValue(':institution_code', $super_inst_code);
        //绑定参数
        if ($code != null && $code != '') {
            $filter_code = '%' . $code . '%';
            $cmd->bindValue(':filter_code', $filter_code);
        }
        if ($name != null && $name != '') {
            $filter_name = '%' . $name . '%';
            $cmd->bindValue(':filter_name', $filter_name);
        }
        if ($brand != null && $brand != '') {
            $filter_brand = '%' . $brand . '%';
            $cmd->bindValue(':filter_brand', $filter_brand);
        }
        if ($is_hide != null && $is_hide != '') {
            $cmd->bindValue(':filter_is_hide', $is_hide);
        }
        //执行获取结果
        $result = $cmd->queryAll();
        return $result;
    }


    /**
     * 变更商品针对旗下机构不可见
     * @param string $super_inst_code
     * @param string $child_inst_code
     * @param string $goods_code
     * @param bool $is_hide [true/false]
     * @return mixed
     */
    public static function changeHideChildInst($super_inst_code, $child_inst_code, $goods_code, $is_hide)
    {
        //调用存储过程 ，当产品是否对旗下机构不可见为
        //‘是’时：1.删除表t_goods_hide_child_institution (商品针对旗下机构不可见)中对应数据；
        //‘否’时：1.向表t_goods_hide_child_institution (商品针对旗下机构不可见)中添加不可见机构及产品信息；
        //          2.删除旗下机构的产品档案信息 并循环旗下机构的所有下级机构删除产品档案信息；

        $sql = "call p_change_goods_hide_child(:in_super_inst_code,:in_child_inst_code,:in_goods_code,:in_is_hide,@ostatus)";

        $cmd = \Yii::$app->db->createCommand($sql);
        $cmd->bindValue(':in_super_inst_code', $super_inst_code);
        $cmd->bindValue(':in_child_inst_code', $child_inst_code);
        $cmd->bindValue(':in_goods_code', $goods_code);
        $cmd->bindValue(':in_is_hide', $is_hide);

        $cmd->execute();

        $cmd = \Yii::$app->db->createCommand('SELECT @ostatus');
        $ostatus = $cmd->queryScalar();

        return $ostatus;
    }

    /**
     * 根据机构编号和产品编号查询产品编码（检查产品是否存在）
     * @param string $institution_code
     * @param string $goods_code
     * @return false|null|string
     */
    public static function getGoodsCodeByInstAndCode($institution_code, $goods_code)
    {
        //查询SQL
        $sql = "select code from t_goods where institution_code=:institution_code and code =:goods_code";

        //创建查询
        $cmd = \Yii::$app->db->createCommand($sql);
        $cmd->bindValue(':institution_code', $institution_code);
        $cmd->bindValue(':goods_code', $goods_code);
        //执行获取结果
        $result = $cmd->queryScalar();
        return $result;
    }

    /**
     * 根据机构编号和产品编号查询上级机构产品编码（检查产品是否存在）
     * @param string $child_institution_code
     * @param string $goods_code
     * @return false|null|string
     */
    public static function getGoodsCodeByChildInstAndCode($child_institution_code, $goods_code)
    {
        //查询SQL
        $sql = "select code from t_goods where institution_code=(select parent_code from t_institution where code =:institution_code) and code =:goods_code";

        //创建查询
        $cmd = \Yii::$app->db->createCommand($sql);
        $cmd->bindValue(':institution_code', $child_institution_code);
        $cmd->bindValue(':goods_code', $goods_code);
        //执行获取结果
        $result = $cmd->queryScalar();
        return $result;
    }

    /**
     * 根据旗下机构编号和产品编号查询是否存在不可见商品
     * @param string $child_institution_code
     * @param string $goods_code
     * @return false|null|string
     */
    public static function getHideGoodsByChildInstAndCode($child_institution_code, $goods_code)
    {
        //查询SQL
        $sql = "select goods_code from t_goods_hide_child_institution where child_institution_code=:child_institution_code and goods_code =:goods_code";

        //创建查询
        $cmd = \Yii::$app->db->createCommand($sql);
        $cmd->bindValue(':child_institution_code', $child_institution_code);
        $cmd->bindValue(':goods_code', $goods_code);
        //执行获取结果
        $result = $cmd->queryScalar();
        return $result;
    }

    /**
     * 修改机构产品信息
     * @param string $institution_code
     * @param string $goods_code
     * @param decimal $settlement_price
     * @param decimal $selling_price
     * @param decimal $supply_price
     * @param string $category_code
     * @param int $is_enabled [0/1]
     * @param int $is_shelved [0/1]
     * @param string /null $shelve_datetime
     * @param int $is_newest [0/1]
     * @param int $is_hot [0/1]
     * @param int $is_top [0/1]
     * @param int $is_show [0/1]
     * @return int
     * @throws \yii\db\Exception
     */
    public static function updateInstGoods($institution_code, $goods_code, $settlement_price, $selling_price, $supply_price, $category_code, $is_enabled, $is_shelved, $shelve_datetime, $is_newest, $is_hot, $is_top, $is_show)
    {
//        $sql = "update t_goods
//                set settlement_price=:settlement_price,
//                    selling_price=:selling_price,
//                    supply_price=:supply_price,
//                    is_enabled=:is_enabled,
//                    is_shelved=:is_shelved,
//                    shelve_datetime=:shelve_datetime,
//                    is_newest=:is_newest,
//                    is_hot=:is_hot,
//                    is_top=:is_top,
//                    is_show=:is_show,
//                    is_modified=1,
//                    modify_datetime=NOW()
//                where institution_code=:institution_code and code=:goods_code";
//        $cmd = \Yii::$app->db->createCommand($sql);
//        $cmd->bindValue(':settlement_price', $settlement_price);
//        $cmd->bindValue(':selling_price', $selling_price);
//        $cmd->bindValue(':supply_price', $supply_price);
//        $cmd->bindValue(':is_enabled', $is_enabled);
//        $cmd->bindValue(':is_shelved', $is_shelved);
//        if ($shelve_datetime == null || $shelve_datetime == '') {
//            $cmd->bindValue(':shelve_datetime', null);
//        } else {
//            $cmd->bindValue(':shelve_datetime', $shelve_datetime);
//        }
//        $cmd->bindValue(':is_newest', $is_newest);
//        $cmd->bindValue(':is_hot', $is_hot);
//        $cmd->bindValue(':is_top', $is_top);
//        $cmd->bindValue(':is_show', $is_show);
//        $cmd->bindValue(':institution_code', $institution_code);
//        $cmd->bindValue(':goods_code', $goods_code);
//
//        $rt = $cmd->execute();
//        if ($rt > 0) {
//            //修改产品分类
//            self::updateInstGoodsCategory($institution_code, $goods_code, $category_code);
//            //修改下级机构商品结算价
//            self::updateChildInstGoodsSettlementPrice($institution_code, $goods_code,$supply_price);
//            return 0;
//        } else {
//            return 1;
//        }
        //20160830改为调用存储过程
        //调用存储过程 ，当产品是否对旗下机构不可见为
        //‘是’时：1.删除表t_goods_hide_child_institution (商品针对旗下机构不可见)中对应数据；
        //‘否’时：1.向表t_goods_hide_child_institution (商品针对旗下机构不可见)中添加不可见机构及产品信息；
        //          2.删除旗下机构的产品档案信息 并循环旗下机构的所有下级机构删除产品档案信息；

        $sql = "call p_update_institution_goods(:in_institution_code,:in_goods_code,:in_settlement_price,:in_selling_price,:in_supply_price,:in_category_code,:in_is_enabled,:in_is_shelved,:in_shelve_datetime,:in_is_newest,:in_is_hot,:in_is_top,:in_is_show,@ostatus)";

        $cmd = \Yii::$app->db->createCommand($sql);
        $cmd->bindValue(':in_institution_code', $institution_code);
        $cmd->bindValue(':in_goods_code', $goods_code);
        $cmd->bindValue(':in_settlement_price', $settlement_price);
        $cmd->bindValue(':in_selling_price', $selling_price);
        $cmd->bindValue(':in_supply_price', $supply_price);
        $cmd->bindValue(':in_category_code', $category_code);
        $cmd->bindValue(':in_is_enabled', $is_enabled);
        $cmd->bindValue(':in_is_shelved', $is_shelved);
        if ($shelve_datetime == null || $shelve_datetime == '') {
            $cmd->bindValue(':in_shelve_datetime', null);
        } else {
            $cmd->bindValue(':in_shelve_datetime', $shelve_datetime);
        }
        $cmd->bindValue(':in_is_newest', $is_newest);
        $cmd->bindValue(':in_is_hot', $is_hot);
        $cmd->bindValue(':in_is_top', $is_top);
        $cmd->bindValue(':in_is_show', $is_show);
        $cmd->execute();

        $cmd = \Yii::$app->db->createCommand('SELECT @ostatus');
        $ostatus = $cmd->queryScalar();

        return $ostatus;
    }

    /**
     * 检查产品是否可以启用上架
     * @param $goods_code
     * @return array|false|null
     */
    public static function checkGoodsCanShelved($goods_code)
    {
        //查询SQL
        $sql = "select case when is_enabled=0 and is_shelved = 0 then 0 else 1 end iscanshelved from t_goods t where t.code =:goods_code and t.institution_code = '0'";
        //创建查询
        $cmd = \Yii::$app->db->createCommand($sql);
        $cmd->bindValue(':goods_code', $goods_code);
        //执行获取结果
        $result = $cmd->queryScalar();

        return $result;

    }

    /**
     * 修改下级机构商品结算价
     * @param $institution_code
     * @param $goods_code
     * @param $supply_price
     * @return int
     * @throws \yii\db\Exception
     */
    public static function updateChildInstGoodsSettlementPrice($institution_code, $goods_code, $supply_price)
    {
        $sql = "update t_goods set settlement_price =:settlement_price where goods_code =:goods_code and institution_code in(select code from t_institution where parent_code = :institution_code) ";
        $cmd = \Yii::$app->db->createCommand($sql);
        $cmd->bindValue(':settlement_price', $supply_price);
        $cmd->bindValue(':goods_code', $goods_code);
        $cmd->bindValue(':institution_code', $institution_code);
        $rt = $cmd->execute();
        return $rt;
    }

    /**
     * 结算价格变动确认
     * @param $institution_code
     * @param $goods_code
     * @return int
     * @throws \yii\db\Exception
     */
    public static function confirmInstGoodsPriceChange($institution_code, $goods_code)
    {
        $sql = "update t_goods_supply_price_records set is_read = 1 where child_institution_code=:institution_code and goods_code=:goods_code ";
        $cmd = \Yii::$app->db->createCommand($sql);
        $cmd->bindValue(':institution_code', $institution_code);
        $cmd->bindValue(':goods_code', $goods_code);
        $rt = $cmd->execute();
        return $rt;
    }

    /**
     * 修改机构产品上下架状态
     * @param string $institution_code
     * @param int $is_shelved [0/1]
     * @param string /null $goods_code
     * @return int
     */
    public static function updateInstGoodsIshelve($institution_code, $is_shelved, $goods_code = null)
    {
        if ($institution_code == null || $institution_code == '' || ($is_shelved != 0 and $is_shelved != 1)) {
            return false;
        }
        $filterSql = "";
        if ($goods_code != null && $goods_code != '') {
            $filterSql = $filterSql . ' AND code = :goods_code ';
        }

        if ($is_shelved == 1) {
            //上架时 禁用产品不做上架修改
            $sql = "update t_goods set is_shelved =1, shelve_datetime=now() where is_enabled!=0 and institution_code =:institution_code $filterSql ";
        } else {
            $sql = "update t_goods set is_shelved =0, shelve_datetime=now() where institution_code =:institution_code $filterSql ";
        }


        $cmd = \Yii::$app->db->createCommand($sql);
        $cmd->bindValue(':institution_code', $institution_code);
        if ($goods_code != null && $goods_code != '') {
            $cmd->bindValue(':goods_code', $goods_code);
        }

        $rt = $cmd->execute();
        return $rt;
    }

    /**
     * 修改机构产品启禁用状态
     * @param string $institution_code
     * @param int $is_enabled [0/1]
     * @param string /null $goods_code
     * @return int
     */
    public static function updateInstGoodsIsEnabled($institution_code, $is_enabled, $goods_code = null)
    {
        if ($institution_code == null || $institution_code == '' || ($is_enabled != 0 and $is_enabled != 1)) {
            return false;
        }
        $filterSql = "";
        if ($goods_code != null && $goods_code != '') {
            $filterSql = $filterSql . ' AND code = :goods_code ';
        }

        if ($is_enabled == 1) {
            //修改启用
            $sql = "update t_goods set is_enabled =1, shelve_datetime=now() where institution_code =:institution_code $filterSql ";
        } else {
            //修改禁用时 同是修改产品为下架
            $sql = "update t_goods set is_enabled =0,is_shelved=0, shelve_datetime=now() where institution_code =:institution_code $filterSql ";
        }


        $cmd = \Yii::$app->db->createCommand($sql);
        $cmd->bindValue(':institution_code', $institution_code);
        if ($goods_code != null && $goods_code != '') {
            $cmd->bindValue(':goods_code', $goods_code);
        }

        $rt = $cmd->execute();
        return $rt;
    }

    /**
     * 修改产品所属分类
     * @param string $institution_code
     * @param string $goods_code
     * @param string $category_code
     * @return int
     * @throws \yii\db\Exception
     */
    public static function updateInstGoodsCategory($institution_code, $goods_code, $category_code)
    {
        $sql = "update t_goods_category_goods set category_code =:category_code where goods_code =:goods_code and institution_code=:institution_code ";
        $cmd = \Yii::$app->db->createCommand($sql);
        $cmd->bindValue(':category_code', $category_code);
        $cmd->bindValue(':goods_code', $goods_code);
        $cmd->bindValue(':institution_code', $institution_code);
        $rt = $cmd->execute();
        return $rt;
    }

    /**
     * 机构产品池导入
     * @param string $institution_code
     * @param array $importGoodsList
     * @return int
     */
    public static function importInstitutionGoods($institution_code, $importGoodsList)
    {
        if (($institution_code != null && $institution_code != '') && ($importGoodsList != null && $importGoodsList != '')) {
            foreach ($importGoodsList as $goods) {
                $goods_code = $goods['goods_code'];
                $settlement_price = $goods['settlement_price'];
                $selling_price = $goods['selling_price'];
                $supply_price = $goods['supply_price'];
                $is_enabled = $goods['is_enabled'];
                $is_shelved = $goods['is_shelved'];
                $is_newest = $goods['is_newest'];
                $is_hot = $goods['is_hot'];
                $is_top = $goods['is_top'];
                $is_show = $goods['is_show'];

                $sql = "call p_save_institution_goods(:in_institution_code,:in_goods_code,:in_settlement_price,:in_selling_price,:in_supply_price,:in_is_enabled,:in_is_shelved,:in_is_newest,:in_is_hot,:in_is_top,:in_is_show)";

                $cmd = \Yii::$app->db->createCommand($sql);
                $cmd->bindValue(':in_institution_code', $institution_code);
                $cmd->bindValue(':in_goods_code', $goods_code);
                $cmd->bindValue(':in_settlement_price', $settlement_price);
                $cmd->bindValue(':in_selling_price', $selling_price);
                $cmd->bindValue(':in_supply_price', $supply_price);
                $cmd->bindValue(':in_is_enabled', $is_enabled);
                $cmd->bindValue(':in_is_shelved', $is_shelved);
                $cmd->bindValue(':in_is_newest', $is_newest);
                $cmd->bindValue(':in_is_hot', $is_hot);
                $cmd->bindValue(':in_is_top', $is_top);
                $cmd->bindValue(':in_is_show', $is_show);

                $cmd->execute();
            }
        }

        return 0;
    }

    /**
     * 查询产品分类数量
     * @param string $institution_code
     * @return false|null|string
     */
    public static function getGoodsCategoryCount($institution_code)
    {
        $result = null;
        if ($institution_code != null && $institution_code != '') {
            //查询SQL
            $sql = "select count(code) from t_goods_category where institution_code=:institution_code ";

            //创建查询
            $cmd = \Yii::$app->db->createCommand($sql);
            $cmd->bindValue(':institution_code', $institution_code);
            //执行获取结果
            $result = $cmd->queryScalar();
        }
        return $result;
    }

    /**
     * 查询产品分类列表
     * @param string $institution_code
     * @return array|null
     */
    public static function getGoodsCategoryList($institution_code)
    {
        $result = null;
        if ($institution_code != null && $institution_code != '') {

            //查询SQL
            $sql = "select * from t_goods_category where institution_code=:institution_code ";

            //创建查询
            $cmd = \Yii::$app->db->createCommand($sql);
            $cmd->bindValue(':institution_code', $institution_code);

            //执行获取结果
            $result = $cmd->queryAll();
        }

        return $result;
    }

    /**
     * 获取包含层级关系的商品分类列表
     * @param $institution_code
     * @return array|null
     */
    public static function getGoodsCategoryListWithHierarchy($institution_code)
    {
        $items = self::getGoodsCategoryListbyParentCode($institution_code, null);
        foreach ($items as &$item) {
            $item['children'] = self::getGoodsCategoryListbyParentCode($institution_code, $item['code']);
            foreach ($item['children'] as &$child) {
                $child['children'] = self::getGoodsCategoryListbyParentCode($institution_code, $child['code']);
            }
        }

        return $items;
    }

    /**
     * 查询产品分类列表
     * @param $institution_code
     * @param $parentCode
     * @return array|null
     */
    public static function getGoodsCategoryListbyParentCode($institution_code, $parentCode)
    {
        $result = null;
        if ($institution_code != null && $institution_code != '') {

            //查询SQL
            $sql = "select * from t_goods_category where institution_code=:institution_code ";
            if ($parentCode == null || $parentCode == '') {
                $sql = $sql . ' and (parent_code is null or parent_code = \'\')';
            } else {
                $sql = $sql . ' and parent_code = :parent_code';
            }

            //创建查询
            $cmd = \Yii::$app->db->createCommand($sql);
            $cmd->bindValue(':institution_code', $institution_code);
            if ($parentCode != null && $parentCode != '') {
                $cmd->bindValue(':parent_code', $parentCode);
            }

            //执行获取结果
            $result = DbCacheUtility::queryAll($cmd, 0);
        }

        return $result;
    }

    /**
     * 获取商品种类
     * @param $institution_code
     * @param $code
     * @return array|false|null
     */
    public static function getGoodsCategory($institution_code, $code)
    {
        $sql = 'SELECT * FROM t_goods_category WHERE institution_code=:institution_code AND code = :code';
        $cmd = \Yii::$app->db->createCommand($sql);
        $cmd->bindValue(':institution_code', $institution_code);
        $cmd->bindValue(':code', $code);

        $result = $cmd->queryOne();
        if ($result == false) {
            return null;
        }

        return $result;
    }

    /**
     * 查询分类名称是否重复
     * @param $institution_code
     * @param $code
     * @param $name
     * @param $parent_code
     * @return array|false|null
     */
    public static function checkGoodsCategoryIsRepeat($institution_code, $code, $name, $parent_code)
    {
        $filterSQL = '';

        //添加查询条件
        if ($code != null && $code != '') {
            $filterSQL = $filterSQL . " and t.code !=:code ";
        }
        if ($parent_code != null && $parent_code != '') {
            $filterSQL = $filterSQL . " and t.parent_code =:parent_code ";
        } else {
            $filterSQL = $filterSQL . ' and t.parent_code = \'\' ';
        }

        //查询SQL
        $sql = "SELECT t.* FROM t_goods_category t WHERE t.institution_code=:institution_code AND t.`name` =:cname $filterSQL ";
        //创建查询
        $cmd = \Yii::$app->db->createCommand($sql);
        $cmd->bindValue(':institution_code', $institution_code);
        $cmd->bindValue(':cname', $name);
        //绑定参数
        if ($code != null && $code != '') {
            $cmd->bindValue(':code', $code);
        }
        if ($parent_code != null && $parent_code != '') {
            $cmd->bindValue(':parent_code', $parent_code);
        }
        //执行获取结果
        $result = $cmd->queryAll();

        if ($result != null && $result != '') {
            return 1;
        } else {
            return 0;
        }

    }

    /**
     * 编辑商品分类 （当code为空是默认为新增 不为空时为修改）
     * @param string $code [null|string]
     * @param string $name
     * @param string $is_show [null|string]
     * @param string $image_url [null|string]
     * @param string $parent_code [null|string]
     * @param string $institution_code
     * @return false|null|string
     * @throws \yii\db\Exception
     */
    public static function editGoodsCategory($code, $name, $is_show, $image_url, $parent_code, $institution_code)
    {
        $sql = "call p_edit_goods_category(:in_code,:in_name,:in_is_show,:in_image_url,:in_parent_code,:in_institution_code,@oid)";


        $cmd = \Yii::$app->db->createCommand($sql);
        $cmd->bindValue(':in_code', $code);
        $cmd->bindValue(':in_name', $name);
        $cmd->bindValue(':in_is_show', $is_show);
        $cmd->bindValue(':in_image_url', $image_url);
        $cmd->bindValue(':in_parent_code', $parent_code);
        $cmd->bindValue(':in_institution_code', $institution_code);

        $cmd->execute();

        $cmd = \Yii::$app->db->createCommand('SELECT @oid');
        $oocode = $cmd->queryScalar();

        return $oocode;

    }

    /**
     * 判断是否有下级种类
     * @param $code
     * @param $institution_code
     * @return false|null|string
     */
    public static function deleteGoodsCategoryCheck($code, $institution_code)
    {
        $cmd = \Yii::$app->db->createCommand('select count(*) from t_goods_category where institution_code = :institution_code and parent_code = :code ');
        $cmd->bindValue(':institution_code', $institution_code);
        $cmd->bindValue(':code', $code);
        $result = $cmd->queryScalar();
        return $result;

    }

    /**
     * 删除机构产品分类
     * @param $code
     * @param $institution_code
     * @return bool|int
     * @throws \yii\db\Exception
     */
    public static function deleteGoodsCategory($code, $institution_code)
    {
        if (($code != null && $code != '') && ($institution_code != null && $institution_code != '')) {

            $connection = \Yii::$app->db;
            $transaction = $connection->beginTransaction();
            try {
                $cmd = \Yii::$app->db->createCommand('delete from t_goods_category_goods where category_code = :category_code and institution_code = :institution_code');
                $cmd->bindValue(':category_code', $code);
                $cmd->bindValue(':institution_code', $institution_code);
                $cmd->execute();

                $cmd = \Yii::$app->db->createCommand('delete from t_goods_category where code=:code and institution_code=:institution_code');
                $cmd->bindValue(':code', $code);
                $cmd->bindValue(':institution_code', $institution_code);

                $result = $cmd->execute();
                $transaction->commit();
                return $result;
            } catch (\Exception $e) {
                $transaction->rollBack();
                return false;
            }
        } else {
            return false;
        }
    }




    /*--------------------------------华丽的分割线，以下方法用于前端商城---------------------------------------------------------------------------------------------------------------------------------------*/
    /**
     * 前端商城获取商品列表
     * @param $institutionCode
     * @param string|null $goodsCode
     * @param string|null $goodsName
     * @param string|null $goodsCategoryCode
     * @param int|null $isNewest [0/1]
     * @param int|null $isHot [0/1]
     * @param int|null $integralForm
     * @param int|null $integralTo
     * @param int|null $sort 1:按价格从小到大; 2:按价格从大到小; 3:按上架时间从新到旧; 4:按上架时间从旧到新
     * @return false|null|int
     */
    public static function getGoodsListCountBySearch($institutionCode, $goodsCode = null, $goodsName = null, $goodsCategoryCode = null, $isNewest = null, $isHot = null, $integralFrom = null, $integralTo = null)
    {
        if ($institutionCode == null || $institutionCode == '') {
            return 0;
        }

        $filterSql = '';
        if (!empty($goodsCode)) {
            $filterSql = $filterSql . " AND t0.`code` LIKE :goods_code "; //' AND t1.code LIKE :goods_code ';
        }
        if (!empty($goodsName)) {
            $filterSql = $filterSql . " AND t0.`name` LIKE :goods_name ";  //' AND t1.name LIKE :goods_name ';
        }
        //$goodsCategorySql = '';
        if (!empty($goodsCategoryCode)) {
//            $goodsCategorySql = ' LEFT JOIN t_goods_category_goods t2 ON t2.category_code=:goods_category_code AND t2.institution_code=t1.institution_code AND t2.goods_code=t1.code ';
//            $filterSql = $filterSql . ' AND t2.category_code=:goods_category_code';
            $filterSql = $filterSql . " AND t1.category_code LIKE :goods_category_code ";
        }
        if (!empty($isNewest)) {
            $filterSql = $filterSql . " AND t0.is_newest = :is_newest ";  //' AND t1.is_newest=:is_newest';
        }
        if (!empty($isHot)) {
            $filterSql = $filterSql . " AND t0.is_hot = :is_hot ";  //' AND t1.is_hot=:is_hot';
        }
        if (!empty($integralFrom)) {
            $filterSql = $filterSql . " AND (t0.selling_price*t2.integral_rate)>=:integral_f ";  //' AND (t1.selling_price*t3.integral_rate)>=:integral_f';
        }
        if (!empty($integralTo)) {
            $filterSql = $filterSql . " AND (t0.selling_price*t2.integral_rate)<=:integral_t ";  //' AND (t1.selling_price*t3.integral_rate)<=:integral_t';
        }

//        switch ($sort) {
//            case 1:  // 按价格从小到大
//                $filterSql = $filterSql . ' ORDER BY t1.selling_price ';
//                break;
//            case 2:  // 按价格从大到小
//                $filterSql = $filterSql . ' ORDER BY t1.selling_price DESC ';
//                break;
//            case 3:  // 按上架时间从新到旧
//                $filterSql = $filterSql . ' ORDER BY t1.shelve_datetime ';
//                break;
//            case 4:  // 按上架时间从旧到新
//                $filterSql = $filterSql . ' ORDER BY t1.shelve_datetime DESC ';
//                break;
//        }

//        $cmd = \Yii::$app->db->createCommand("
//            SELECT COUNT(1)
//            FROM t_goods  t1
//             $goodsCategorySql
//            LEFT JOIN t_institution t3 ON t3.`code`=t1.institution_code
//            LEFT JOIN t_goods_image t4 ON t4.goods_code=t1.`code` AND t4.type=1
//            WHERE t1.institution_code=:institution_code
//            AND t1.is_enabled=1
//            AND t1.is_shelved=1
//            $filterSql");
        $cmd = Yii::$app->db->createCommand("
            SELECT COUNT(1)
            FROM (SELECT * FROM t_goods WHERE institution_code=:institution_code GROUP BY group_code) t0
            LEFT JOIN t_goods_category_goods t1 ON t1.institution_code=t0.institution_code AND t1.goods_code=t0.`code`
            LEFT JOIN t_institution t2 ON t2.`code`=t0.institution_code
            WHERE t0.is_enabled=1
            AND t0.is_shelved=1
            AND t0.is_activity=0
            AND t0.is_show=1
            $filterSql");
        $cmd->bindValue(':institution_code', $institutionCode);

        if (!empty($goodsCode)) {
            $cmd->bindValue(':goods_code', '%' . $goodsCode . '%');
        }
        if (!empty($goodsName)) {
            $cmd->bindValue(':goods_name', '%' . $goodsName . '%');
        }
        if (!empty($goodsCategoryCode)) {
            $cmd->bindValue(':goods_category_code', $goodsCategoryCode . '%');
        }
        if (!empty($isNewest)) {
            $cmd->bindValue(':is_newest', $isNewest);
        }
        if (!empty($isHot)) {
            $cmd->bindValue(':is_hot', $isHot);
        }
        if (!empty($integralFrom)) {
            $cmd->bindValue(':integral_f', $integralFrom);
        }
        if (!empty($integralTo)) {
            $cmd->bindValue(':integral_t', $integralTo);
        }

        $count = DbCacheUtility::queryScalar($cmd);
        return $count;
    }

    /**
     * 前端商城获取商品列表
     * @param $institutionCode
     * @param int $page
     * @param int $pageSize
     * @param string|null $goodsCode
     * @param string|null $goodsName
     * @param string|null $goodsCategoryCode
     * @param int|null $isNewest [0/1]
     * @param int|null $isHot [0/1]
     * @param int|null $integralForm
     * @param int|null $integralTo
     * @param int|null $sort 1:按价格从小到大; 2:按价格从大到小; 3:按上架时间从新到旧; 4:按上架时间从旧到新
     * @return array|null
     */
    public static function getGoodsListBySearch($institutionCode, $page = 1, $pageSize = 10, $goodsCode = null, $goodsName = null, $goodsCategoryCode = null, $isNewest = null, $isHot = null, $integralFrom = null, $integralTo = null, $sort = null)
    {
        if ($institutionCode == null || $institutionCode == '') {
            return [];
        }

        $filterSql = '';
        if (!empty($goodsCode)) {
            $filterSql = $filterSql . " AND t0.`code` LIKE :goods_code ";
        }
        if (!empty($goodsName)) {
            $filterSql = $filterSql . " AND t0.`name` LIKE :goods_name ";
        }
        if (!empty($goodsCategoryCode)) {
            $filterSql = $filterSql . " AND t1.category_code LIKE :goods_category_code ";
        }
        if (!empty($isNewest)) {
            $filterSql = $filterSql . " AND t0.is_newest = :is_newest ";
        }
        if (!empty($isHot)) {
            $filterSql = $filterSql . " AND t0.is_hot = :is_hot ";
        }
        if (!empty($integralFrom)) {
            $filterSql = $filterSql . " AND (t0.selling_price*t2.integral_rate)>=:integral_f ";
        }
        if (!empty($integralTo)) {
            $filterSql = $filterSql . " AND (t0.selling_price*t2.integral_rate)<=:integral_t ";
        }
//
//
//        if ($goodsCode != null && $goodsCode != '') {
//            $filterSql = $filterSql . ' AND t1.code LIKE :goods_code ';
//        }
//        if ($goodsName != null && $goodsName != '') {
//            $filterSql = $filterSql . ' AND t1.name LIKE :goods_name ';
//        }
//        $goodsCategorySql = '';
//        if ($goodsCategoryCode != null && $goodsCategoryCode != '') {
//            $goodsCategorySql = ' LEFT JOIN t_goods_category_goods t2 ON t2.category_code=:goods_category_code AND t2.institution_code=t1.institution_code AND t2.goods_code=t1.code ';
//            $filterSql = $filterSql . ' AND t2.category_code=:goods_category_code';
//        }
//        if ($isNewest != null && $isNewest != '') {
//            $filterSql = $filterSql . ' AND t1.is_newest=:is_newest';
//        }
//        if ($isHot != null && $isHot != '') {
//            $filterSql = $filterSql . ' AND t1.is_hot=:is_hot';
//        }
//        if ($integralFrom != null && $integralFrom != '') {
//            $filterSql = $filterSql . ' AND (t1.selling_price*t3.integral_rate)>=:integral_f';
//        }
//        if ($integralTo != null && $integralTo != '') {
//            $filterSql = $filterSql . ' AND (t1.selling_price*t3.integral_rate)<=:integral_t';
//        }

        switch ($sort) {
            case 1:  // 按价格从小到大
                $filterSql = $filterSql . ' ORDER BY t0.selling_price ';
                break;
            case 2:  // 按价格从大到小
                $filterSql = $filterSql . ' ORDER BY t0.selling_price DESC ';
                break;
            case 3:  // 按上架时间从新到旧
                $filterSql = $filterSql . ' ORDER BY t0.shelve_datetime ';
                break;
            case 4:  // 按上架时间从旧到新
                $filterSql = $filterSql . ' ORDER BY t0.shelve_datetime DESC ';
                break;
        }

        $page = $page < 1 ? 1 : $page;
        $offset = ($page - 1) * $pageSize;
//        $cmd = \Yii::$app->db->createCommand("
//            SELECT t1.`code`, t1.institution_code, t1.`name`, t1.model, t1.brand, t1.selling_price, t1.selling_price*t3.integral_rate AS selling_integral, t1.type, t1.bind_type, t1.is_newest, t1.is_hot, t1.is_top, t1.is_activity, t1.is_trusteeship, t4.image_url AS goods_image
//            FROM t_goods  t1
//             $goodsCategorySql
//            LEFT JOIN t_institution t3 ON t3.`code`=t1.institution_code
//            LEFT JOIN t_goods_image t4 ON t4.goods_code=t1.`code` AND t4.type=1
//            WHERE t1.institution_code=:institution_code
//            AND t1.is_enabled=1
//            AND t1.is_shelved=1
//            $filterSql
//             LIMIT :offset, :limit");
        $cmd = Yii::$app->db->createCommand("
            SELECT t0.`code`, t0.`name`, t0.display_name, t0.group_code, t0.group_item_name, t0.model, t0.format, t0.brand, t0.selling_price, t0.selling_price*t2.integral_rate as selling_integral, t2.integral_unit, t0.shelve_datetime, t0.is_newest, t0.is_hot, t0.is_top, t0.is_trusteeship, t0.is_crossborder, t3.image_url AS goods_image
            FROM (SELECT * FROM t_goods WHERE institution_code=:institution_code GROUP BY group_code) t0
            LEFT JOIN t_goods_category_goods t1 ON t1.institution_code=t0.institution_code AND t1.goods_code=t0.`code`
            LEFT JOIN t_institution t2 ON t2.`code`=t0.institution_code
            LEFT JOIN t_goods_image t3 ON t3.goods_code=t0.`code` AND t3.type=1
            WHERE t0.is_enabled=1
            AND t0.is_shelved=1
            AND t0.is_activity=0
            AND t0.is_show=1
            $filterSql
            LIMIT :offset, :limit");
        $cmd->bindValue(':institution_code', $institutionCode);
        $cmd->bindValue(':offset', $offset);
        $cmd->bindValue(':limit', $pageSize);

        if (!empty($goodsCode)) {
            $cmd->bindValue(':goods_code', '%' . $goodsCode . '%');
        }
        if (!empty($goodsName)) {
            $cmd->bindValue(':goods_name', '%' . $goodsName . '%');
        }
        if (!empty($goodsCategoryCode)) {
            $cmd->bindValue(':goods_category_code', $goodsCategoryCode . '%');
        }
        if (!empty($isNewest)) {
            $cmd->bindValue(':is_newest', $isNewest);
        }
        if (!empty($isHot)) {
            $cmd->bindValue(':is_hot', $isHot);
        }
        if (!empty($integralFrom)) {
            $cmd->bindValue(':integral_f', $integralFrom);
        }
        if (!empty($integralTo)) {
            $cmd->bindValue(':integral_t', $integralTo);
        }

        $result = $cmd->queryAll();
        return $result;
    }

    /**
     * 前端商城获取商品库存
     * @param string $goodsCode
     * @return mixed
     */
    public static function getGoodsStock($goodsCode)
    {
        return Inteface::getGoodsStock($goodsCode);
    }

//------------------------------扩展分类start--------------
//检查数据
    public static function editGoodsExtendCategory_check_name($institution_code, $name)
    {
        //分类名称是否重复
        $cmd = \Yii::$app->db->createCommand('select count(1) from t_goods_extend_category where institution_code=:institution_code and name=:name');
        $cmd->bindValue(':institution_code', $institution_code);
        $cmd->bindValue(':name', $name);
        return $cmd->queryScalar() == '0';
        //分类定义标识符 是否重复
    }

    public static function editGoodsExtendCategory_check_identifier_code($institution_code, $identifier_code)
    {
        //分类定义标识符 是否重复
        $cmd = \Yii::$app->db->createCommand('select count(1) from t_goods_extend_category where institution_code=:institution_code and identifier_code=:identifier_code');
        $cmd->bindValue(':institution_code', $institution_code);
        $cmd->bindValue(':identifier_code', $identifier_code);
        return $cmd->queryScalar() == '0';

    }

    //编辑商品扩展分类 （当id为空是默认为新增 不为空时为修改）
    public static function editGoodsExtendCategory($id, $name, $institution_code, $summary, $image_url, $parent_id, $type, $link_url, $remark, $is_enabled, $identifier_code,$endpoint)
    {
        //新增
        if ($id) {
            $sql = "update  t_goods_extend_category set
             name=:name,summary=:summary,image_url=:image_url,parent_id=:parent_id,type=:type,
             link_url=:link_url,remark=:remark,is_enabled=:is_enabled,modify_datetime=now(),identifier_code=:identifier_code,endpoint=:endpoint
             where id=:id";
            $cmd = \Yii::$app->db->createCommand($sql);
            $cmd->bindValue(':id', $id);
            $cmd->bindValue(':name', $name);
            $cmd->bindValue(':summary', $summary);
            $cmd->bindValue(':image_url', $image_url);
            $cmd->bindValue(':parent_id', $parent_id);
            $cmd->bindValue(':type', $type);
            $cmd->bindValue(':link_url', $link_url);
            $cmd->bindValue(':remark', $remark);
            $cmd->bindValue(':is_enabled', $is_enabled);
            $cmd->bindValue(':identifier_code', $identifier_code);
            $cmd->bindValue(':endpoint', $endpoint);
            return $cmd->execute();
        } else {
            $id = Tools::db_uuid();

            $sql = "insert into t_goods_extend_category
 (id,institution_code,name,summary,image_url,parent_id,type,link_url,remark,is_enabled,is_delete,create_datetime,modify_datetime,is_sync,identifier_code,endpoint)
   VALUES
   (:id,:institution_code,:name,:summary,:image_url,:parent_id,:type,:link_url,:remark,:is_enabled,0,now(),null,0,:identifier_code,:endpoint) ";
            $cmd = \Yii::$app->db->createCommand($sql);
            $cmd->bindValue(':id', $id);
            $cmd->bindValue(':institution_code', $institution_code);
            $cmd->bindValue(':name', $name);
            $cmd->bindValue(':summary', $summary);
            $cmd->bindValue(':image_url', $image_url);
            $cmd->bindValue(':parent_id', $parent_id);
            $cmd->bindValue(':type', $type);
            $cmd->bindValue(':link_url', $link_url);
            $cmd->bindValue(':remark', $remark);
            $cmd->bindValue(':is_enabled', $is_enabled);
            $cmd->bindValue(':identifier_code', $identifier_code);
            $cmd->bindValue(':endpoint', $endpoint);
            $cmd->execute();


            return $id;
        }
    }

    //新增产品扩展分类下属产品
    public static function addGoodsExtendCategoryGoods($extend_category_id, $goods_code)
    {
        $status = 0;
        $transaction = \Yii::$app->db->beginTransaction();
        try {
            self::delGoodsExtendCategoryGoods($extend_category_id, $goods_code);
            $cmd = \Yii::$app->db->createCommand("insert into t_goods_extend_category_goods ( extend_category_id,goods_code) VALUES (:extend_category_id,:goods_code)");
            $cmd->bindValue(":extend_category_id", $extend_category_id);
            $cmd->bindValue(":goods_code", $goods_code);
            $cmd->execute();
            $transaction->commit();
        } catch (\Exception $e) {
            $transaction->rollBack();
            $status = 1;
        }

        return $status;
    }

    //BAT新增产品扩展分类下属产品
    public static function addGoodsExtendCategoryGoodsBat($extend_category_id, $goods_codes)
    {
        $status = 0;
        $transaction = \Yii::$app->db->beginTransaction();
        try {
            foreach ($goods_codes as $goods_code) {
                self::delGoodsExtendCategoryGoods($extend_category_id, $goods_code);
                $cmd = \Yii::$app->db->createCommand("insert into t_goods_extend_category_goods ( extend_category_id,goods_code) VALUES (:extend_category_id,:goods_code)");
                $cmd->bindValue(":extend_category_id", $extend_category_id);
                $cmd->bindValue(":goods_code", $goods_code);
                $cmd->execute();
            }

            $transaction->commit();
        } catch (\Exception $e) {
            $transaction->rollBack();
            $status = 1;
        }

        return $status;
    }

    ////删除产品扩展分类下属产品
    public static function delGoodsExtendCategoryGoods($extend_category_id, $goods_code)
    {
        $cmd = \Yii::$app->db->createCommand("delete from t_goods_extend_category_goods where extend_category_id=:extend_category_id and goods_code=:goods_code");
        $cmd->bindValue(":extend_category_id", $extend_category_id);
        $cmd->bindValue(":goods_code", $goods_code);
        $cmd->execute();
    }

//BAT删除产品扩展分类下属产品
    public static function delGoodsExtendCategoryGoodsBat($extend_category_id, $goods_codes)
    {
        $status = 0;
        $transaction = \Yii::$app->db->beginTransaction();
        try {
            foreach ($goods_codes as $goods_code) {
                self::delGoodsExtendCategoryGoods($extend_category_id, $goods_code);
            }

            $transaction->commit();
        } catch (\Exception $e) {
            $transaction->rollBack();
            $status = 1;
        }

        return $status;
    }

    ////删除产品扩展分类所有下属产品
    public static function delAllGoodsExtendCategoryGoods($extend_category_id)
    {
        $cmd = \Yii::$app->db->createCommand("delete from t_goods_extend_category_goods where extend_category_id=:extend_category_id");
        $cmd->bindValue(":extend_category_id", $extend_category_id);
        $cmd->execute();
    }

    //清除非本机构扩展分类产品
    public static function clearGoodsExtendCategoryGoods($extend_category_id)
    {
        $sql = "delete from t_goods_extend_category_goods where extend_category_id=:extend_category_id
      and not exists( select * from t_goods where institution_code in (select institution_code from t_goods_extend_category  where id=:extend_category_id )
       and goods_code=t_goods.code)";
        $cmd = \Yii::$app->db->createCommand($sql);
        $cmd->bindValue(":extend_category_id", $extend_category_id);
        $cmd->execute();
    }
    //产品扩展分类下属产品是否首页显示
    public static function setGoodsExtendCategoryGoodsIsIndexShow($extend_category_id, $goods_code,$is_index_show)
    {
            $cmd = \Yii::$app->db->createCommand("UPDATE t_goods_extend_category_goods SET is_index_show=:is_index_show
              where extend_category_id=:extend_category_id and goods_code=:goods_code");
            $cmd->bindValue(":extend_category_id", $extend_category_id);
            $cmd->bindValue(":goods_code", $goods_code);
            $cmd->bindValue(":is_index_show", $is_index_show);
            return $cmd->execute();
    }
    /**
     * 删除产品扩展分类
     * @param $id
     * @param $institution_code
     * @return bool|int
     * @throws \yii\db\Exception
     */
    public static function deleteGoodsExtendCategory($extend_category_id)
    {

        $transaction = \Yii::$app->db->beginTransaction();
        try {
            self::delAllGoodsExtendCategoryGoods($extend_category_id);
            $cmd = \Yii::$app->db->createCommand('delete from t_goods_extend_category where id=:extend_category_id');
            $cmd->bindValue(':extend_category_id', $extend_category_id);
            $cmd->execute();
            $transaction->commit();
        } catch (\Exception $e) {
            $transaction->rollBack();
            return false;
        }

        return true;
    }

    /**
     * 查询商品扩展分类列表
     * @param string $institution_code
     * @return array|null
     */
    public static function getGoodsExtendCategoryList($institution_code,$type=-1)
    {
        $result = null;
        if ($institution_code != null && $institution_code != '') {
            $typeSql='';
            if($type=='1'){
                $typeSql=' and type=1';
            }
            if($type=='2'){
                $typeSql=' and type=2';
            }
            //查询SQL
            $sql = "select * from t_goods_extend_category where institution_code=:institution_code $typeSql order by identifier_code desc";

            //创建查询
            $cmd = \Yii::$app->db->createCommand($sql);
            $cmd->bindValue(':institution_code', $institution_code);

            //执行获取结果
            $result = $cmd->queryAll();
        }

        return $result;
    }

    /**
     * 根据机构编号和ID查询商品扩展分类详情
     * @param int $id
     * @param string $institution_code
     * @return array
     */
    public static function getGoodsExtendCategoryDetails($id, $institution_code)
    {
        //查询SQL
        $sql = "select * from t_goods_extend_category where id=:id and institution_code=:institution_code ";
        //创建查询
        $cmd = \Yii::$app->db->createCommand($sql);
        $cmd->bindValue(':id', $id);
        $cmd->bindValue(':institution_code', $institution_code);
        //执行获取结果
        $inst_0 = $cmd->queryAll();
        if ($inst_0 != false) {
            return $inst_0[0];
        } else {
            return $inst_0;
        }
    }

    /**返回当前机构的某级产品分类
     * @param $id  扩展分类id
     * @return array
     */
    public static function getGoodsCategoryByLength($id,$code_length)
    {
        //查询SQL
        $sql = "select * from t_goods_category where length(code)=:code_length and is_show=1
                    and institution_code=(select institution_code from t_goods_extend_category where id =:id) ";
        //创建查询
        $cmd = \Yii::$app->db->createCommand($sql);
        $cmd->bindValue(':id', $id);
        $cmd->bindValue(':code_length', $code_length);
        //执行获取结果
        return $cmd->queryAll();

    }
    /**返回当前机构的某级产品子分类
     * @param $id  扩展分类id
     * @return array
     */
    public static function getGoodsCategoryNext($institution_code,$parent_code)
    {
        //查询SQL
        $sql = "select * from t_goods_category where parent_code=:parent_code and is_show=1
                    and institution_code=:institution_code ";
        //创建查询
        $cmd = \Yii::$app->db->createCommand($sql);
        $cmd->bindValue(':institution_code', $institution_code);
        $cmd->bindValue(':parent_code', $parent_code);
        //执行获取结果
        return $cmd->queryAll();

    }
    public static function getInstitutionCodeByGoods_extend_categoryId($id){
        //查询机构代码
        $cmd = \Yii::$app->db->createCommand('select institution_code from t_goods_extend_category where id =:id');
        $cmd->bindValue(':id',$id);
        $institution_code=$cmd->queryScalar();
        if($institution_code===false){
            $institution_code='-1';
        }
        return $institution_code;
    }
    /**
     * 通过传入的  扩展分类id 产品编号 名称 品牌条件查询出 产品列表 是否已选择  一级分类  价格开始 价格截止
     * @param string $code
     * @param string $name
     * @param string $brand
     * @return array
     */
    public static function getGoodsExtendCategoryGoods($id, $code, $name, $brand, $selected, $categoryV1,$categoryV2,$categoryV3, $priceS, $priceE,$extend_category,$ncTagId)
    {
        //查询机构代码

        $institution_code=self::getInstitutionCodeByGoods_extend_categoryId($id);
        $filterSQL = '';

        //添加查询条件
        if ($code != null && $code != '') {
            $filterSQL = $filterSQL . " and t.code like :filter_code ";
        }
        if ($name != null && $name != '') {
            $filterSQL = $filterSQL . " and t.name like :filter_name ";
        }
        if ($brand != null && $brand != '') {
            $filterSQL = $filterSQL . " and t.brand like :filter_brand ";
        }
        if ($selected != null && $selected != '') {
            $filterSQL = $filterSQL . " and case when h.goods_code is not null then 1 else 0 end =:filter_is_hide ";
        }

        if ($categoryV1 != null && $categoryV1 != '') {
            $filterSQL = $filterSQL . " and t0.category_v1=:category_v1 ";
        }
        if ($categoryV2 != null && $categoryV2 != '') {
            $filterSQL = $filterSQL . " and t0.category_v2=:category_v2 ";
        }
        if ($categoryV3 != null && $categoryV3 != '') {
            $filterSQL = $filterSQL . " and t0.category_v3=:category_v3 ";
        }
        if ($priceS != null && $priceS != '') {
            $filterSQL = $filterSQL . " and t.selling_price >=:priceS ";
        }
        if ($priceE != null && $priceE != '') {
            $filterSQL = $filterSQL . " and t.selling_price <=:priceE ";
        }
        $extend_categorySql='';
        if ($extend_category != null && $extend_category != '') {
            $extend_categorySql=' and not exists (select extend_category_id from t_goods_extend_category_goods where extend_category_id=:extend_category and goods_code = t.code)';
        }
        $ncTagIdSql="";
        if ($ncTagId != null && $ncTagId != '') {
            $ncTagIdSql=" LEFT JOIN t_goods_tag_goods t5 on t5.goods_code=t.code ";
            $filterSQL = $filterSQL . " and t5.tag_id=:ncTagId";
        }
        //查询SQL
        $sql = "select t.institution_code,t.code,t.name,t.brand,t.selling_price,t0.category_v1,case when h.goods_code is not null then 1 else 0 end as is_hide
              , h.is_index_show,t4.image_url,t.is_crossborder from
                (
                select * from t_goods   where  institution_code=:institution_code  AND is_enabled=1  AND is_shelved=1
                ) t
             LEFT JOIN  (SELECT goods_code,category_code,left(category_code,2) category_v1,left(category_code,4) category_v2,left(category_code,6) category_v3
             from  t_goods_category_goods  where  institution_code=:institution_code) t0 on  t.code=t0.goods_code

            LEFT JOIN t_goods_image t4 ON t4.goods_code=t.`code` AND t4.type=1

           LEFT JOIN  (select g.goods_code,g.is_index_show,c.* from t_goods_extend_category c left join t_goods_extend_category_goods g   on g.extend_category_id=c.id
          where  c.id =:id) h  on  h.goods_code = t.code

           $ncTagIdSql

        where t.institution_code=:institution_code  $filterSQL $extend_categorySql order by t.code,case when h.goods_code is not null then 1 else 0 end desc";
        //创建查询
        $cmd = \Yii::$app->db->createCommand($sql);
        $cmd->bindValue(":id", $id);
        $cmd->bindValue(":institution_code", $institution_code);
        //绑定参数
        if ($code != null && $code != '') {
            $filter_code = '%' . $code . '%';
            $cmd->bindValue(':filter_code', $filter_code);
        }
        if ($name != null && $name != '') {
            $filter_name = '%' . $name . '%';
            $cmd->bindValue(':filter_name', $filter_name);
        }
        if ($brand != null && $brand != '') {
            $filter_brand = '%' . $brand . '%';
            $cmd->bindValue(':filter_brand', $filter_brand);
        }
        if ($selected != null && $selected != '') {
            $cmd->bindValue(':filter_is_hide', $selected);
        }
        if ($categoryV1 != null && $categoryV1 != '') {
            $cmd->bindValue(':category_v1', $categoryV1);
        }
        if ($categoryV2 != null && $categoryV2 != '') {
            $cmd->bindValue(':category_v2', $categoryV2);
        }
        if ($categoryV3 != null && $categoryV3 != '') {
            $cmd->bindValue(':category_v3', $categoryV3);
        }
        if ($priceS != null && $priceS != '') {
            $cmd->bindValue(':priceS', $priceS);
        }
        if ($priceE != null && $priceE != '') {
            $cmd->bindValue(':priceE', $priceE);
        }
        if ($extend_category != null && $extend_category != '') {
            $cmd->bindValue(':extend_category', $extend_category);
        }
        if ($ncTagId != null && $ncTagId != '') {
            $cmd->bindValue(':ncTagId', $ncTagId);
        }

        //执行获取结果
        $result = $cmd->queryAll();
//        echo ($cmd->getRawSql());exit;
        return $result;
    }

    /** 同步上一级商品扩展分类
     * @param $institution_code
     * @return bool
     * @throws \yii\db\Exception
     */
    public static function synGoodsGoodsCategory($institution_code)
    {
        //查询父机构

        $cmd = \Yii::$app->db->createCommand('select parent_code from t_institution where code =:institution_code');
        $cmd->bindValue(':institution_code', $institution_code);
        $parent_institution_code = $cmd->queryScalar();
//        echo $cmd->getRawSql();exit;
        if ($parent_institution_code == null || $parent_institution_code == '') {
            return 10;//没有上一级机构
        }
        //执行导入动作
        $transaction = \Yii::$app->db->beginTransaction();
        try {
            //清空已经存在的同步数据
            $cmd = \Yii::$app->db->createCommand('delete  from t_goods_extend_category_goods  where exists (select * from t_goods_extend_category c where c.id=t_goods_extend_category_goods.extend_category_id and c.institution_code= :institution_code  and c.is_sync=1)  ');
            $cmd->bindValue(':institution_code', $institution_code);//本机构数据
            $cmd->execute();
            $cmd = \Yii::$app->db->createCommand('delete from t_goods_extend_category where is_sync=1 and   institution_code =:institution_code');
            $cmd->bindValue(':institution_code', $institution_code);//本机构数据
            $cmd->execute();
            //获取主表数据
            $cmd = \Yii::$app->db->createCommand('select uuid() new_id,c.* from t_goods_extend_category c where     institution_code = :parent_institution_code');
            $cmd->bindValue(':parent_institution_code', $parent_institution_code);//上级机构数据
            $goods_extend_category = $cmd->queryAll();
            foreach ($goods_extend_category as $category) {
                //插入主表数据
                $sql = "insert into t_goods_extend_category
                    (id,institution_code,name,summary,image_url,parent_id,type
                    ,link_url,remark,is_enabled,is_delete,create_datetime,modify_datetime,is_sync,identifier_code,endpoint) VALUES
                    (:new_id,:institution_code,:name,:summary,:image_url,:parent_id,:type,:link_url,:remark,:is_enabled,0,now(),null,1,:identifier_code,:endpoint) ";
                $cmd = \Yii::$app->db->createCommand($sql);
                $cmd->bindValue(':new_id', $category['new_id']);
                $cmd->bindValue(':institution_code', $institution_code);
                $cmd->bindValue(':name', $category['name']);
                $cmd->bindValue(':summary', $category['summary']);
                $cmd->bindValue(':image_url', $category['image_url']);
                $cmd->bindValue(':parent_id', self::getNewidForParentId($goods_extend_category, $category['parent_id']));//找到新的parent_id(从$goods_extend_category循环查找id=parent_id的new_id)
                $cmd->bindValue(':type', $category['type']);
                $cmd->bindValue(':link_url', $category['link_url']);
                $cmd->bindValue(':remark', $category['remark']);
                $cmd->bindValue(':is_enabled', $category['is_enabled']);
                $cmd->bindValue(':identifier_code', $category['identifier_code']);
                $cmd->bindValue(':endpoint', $category['endpoint']);
                $cmd->execute();
                //插入从表数据
                $cmd = \Yii::$app->db->createCommand('insert into t_goods_extend_category_goods (extend_category_id,goods_code)
                       SELECT :new_id,goods_code FROM t_goods_extend_category_goods  where extend_category_id =:extend_category_id');
                $cmd->bindValue(':new_id', $category['new_id']);
                $cmd->bindValue(':extend_category_id', $category['id']);
                $cmd->execute();
                //清除非本机构扩展分类产品
                self::clearGoodsExtendCategoryGoods($category['new_id']);
            }
            $transaction->commit();
        } catch (\Exception $e) {
            $transaction->rollBack();
            throw $e;
            return 1;
        }

        return 0;
    }

    private static function getNewidForParentId($goods_extend_category, $parent_id)
    {
        if ($parent_id) {
            foreach ($goods_extend_category as $category) {
                if ($parent_id == $category['id']) {
                    return $category['new_id'];
                }
            }
        }
        return null;
    }

    //------------------------------扩展分类end--------------

    /**
     * 获取某机构所有置于首页的特价商品列表
     * @param $institutionCode
     * @return array
     */
    public static function getBargainTopGoodsListByInstitution($institutionCode)
    {
        $cmd = Yii::$app->db->createCommand('
            SELECT t1.*, t2.image_url FROM t_activity_bargain t0
            INNER JOIN t_activity_bargain_goods t1 ON t1.activity_id=t0.id
            left JOIN t_goods_image t2 ON t2.goods_code=t1.goods_code and t2.type=1
            WHERE 
            t0.is_enabled=1 AND 
            t0.is_deleted=0 AND 
            t0.start_datetime<NOW() AND 
            t0.end_datetime>NOW() AND 
            t1.is_top=1 AND 
            t0.institution_code=:institutionCode');
        $cmd->bindValue(':institutionCode', $institutionCode);
        $items = $cmd->queryAll();
        return $items;
    }
    //------------nc扩展分类-------------------
    public static function synGoodsTag(){
        $transaction = \Yii::$app->db->beginTransaction();

        try {
            //清空数据表
            $sql="delete from t_goods_tag";
            $cmd = Yii::$app->db->createCommand($sql);
            $cmd->execute();
            $sql="delete from t_goods_tag_goods";
            $cmd = Yii::$app->db->createCommand($sql);
            $cmd->execute();
            //tag
//              public 'id' => string '0001T110000000095JGP' (length=20)
//              public 'code' => string 'KZ01' (length=4)
//              public 'name' => string '创意新品' (length=12)
            $items=Inteface::getNcGoodsTag();
            foreach($items as $item){
                $sql="insert into t_goods_tag (id,code,name) VALUES (:id,:code,:name)";
                $cmd = Yii::$app->db->createCommand($sql);
                $cmd->bindValue(':id',$item->id);
                $cmd->bindValue(':code',$item->code);
                $cmd->bindValue(':name',$item->name);
                $cmd->execute();
            }
            //tag_goods
//            public 'code' => string 'DLHXB0107' (length=9)
        //  public 'cate' =>
        //    array (size=1)
        //      0 =>
        //        object(stdClass)[65]
        //          public 'cateid' => string '0001T110000000095JGR' (length=20)
            $items=Inteface::getNcGoodsTagGoods();
            foreach($items as $item){
                $sql="insert into t_goods_tag_goods (tag_id,goods_code) VALUES (:tag_id,:goods_code)";
                $cmd = Yii::$app->db->createCommand($sql);
                $cmd->bindValue(':tag_id',$item->cate[0]->cateid);
                $cmd->bindValue(':goods_code',$item->code);
                $cmd->execute();
            }
            $transaction->commit();
        } catch (\Exception $e) {
            $transaction->rollBack();
            throw $e;
        }

    }
    //获取所有有产品的tag列表
    public static function getGoodsTagUsedList(){
        $sql=" select * from t_goods_tag c0
      where exists ( select * from t_goods_tag_goods c where c.tag_id=c0.id )";
        $cmd = Yii::$app->db->createCommand($sql);
        return $cmd->queryAll();
    }
    //获取所有有产品的tag产品列表
    public static function getGoodsTagGoodsList($tag_id,$offset, $limit,$result_type='count'){
        $filterSQL = '';

        //添加查询条件
        if ($tag_id != null && $tag_id != '') {
            $filterSQL = $filterSQL . " and t.tag_id=:tag_id ";
        }
        $sql=" select t.name cate_name,t.goods_code,t.code as cate_code,t4.image_url,t1.name goods_name from
(select * from t_goods_tag_goods c,t_goods_tag c0  where c.tag_id=c0.id) t
    LEFT JOIN (select * from t_goods where institution_code='0') t1 on t1.code=t.goods_code
    LEFT JOIN t_goods_image t4 ON t4.goods_code=t.goods_code AND t4.type=1
     where 1=1 $filterSQL order by t.tag_id";
        if ($result_type=='count') {
            $exec_sql = "select count(1) from ($sql) as tt";
        }
        if ($result_type=='list') {
            $exec_sql = "select * from ($sql) as tt LIMIT :offset, :limit";
        }
        $cmd = Yii::$app->db->createCommand($exec_sql);
        //绑定参数
        if ($result_type=='list') {
            $cmd->bindValue(':offset', $offset);
            $cmd->bindValue(':limit', $limit);
        }
        if ($tag_id != null && $tag_id != '') {
            $cmd->bindValue(':tag_id', $tag_id);
        }
        if ($result_type=='count') {
            return $cmd->queryScalar();
        }
        if ($result_type=='list') {
            return $cmd->queryAll();
        }
        return false;
    }
}
